Specify data ranges in logical tests

Topic

New Reply

Hi To Everyone,

I searched for but did not find a thread/post for the following:

How do you check for either a continuous or discontinuous horizontal or vertical range or sequence of cells when conducting a logical test such as =if(or(…..),1,0) ?

I’ve tried the D2:D34 format no luck, any suggestions or help?

Thank you…
Marty

Reply | Quote

Replies

I do not completely understand what you are after can you elaborate?

Steve

Reply | Quote

I do not completely understand what you are after can you elaborate?

Steve

Hi Steve,

I don’t know how the smiley face got in there sorry…. I want to check the values in a range of horizontal or vertical cells either continuous or not; I attached an excel sheet; so if I want to check the values in each row i.e. if(or(b2:r20.7),1,0) for each row, it get the standard formula error window…..maybe I have a brain cramp…thought you could do it in excel. Along with this I would like to do the same for a discontinuous range of cells.

Thanks for your time

Marty

Reply | Quote

Are you looking for something like?:
=IF(OR(MIN(B2:R2)0.7),1,0)

Steve

Reply | Quote

Are you looking for something like?:
=IF(OR(MIN(B2:R2)0.7),1,0)

Steve

Hi Steve,

So it is the additional parentheses…what does the max min get me? In this case I am interested in the data outside or beyond the -0.7 – 0.7 spec.

Thank you for your help

Marty

Reply | Quote

I am confused. I thought you wanted to know if a RANGE of cells (B2:R2) had any cells that are outside the range. The Min(B2:R2) gives the minimum in that range, Max(B2:R2) gives the max. If either the min is .7 then there is at least one cell in the range that is too low or too high. The formula does not specify which cell, but you did not seem interested in that…

If you are you looking for an individual cell then use something with a formula for just one cell like:
=IF(OR(B20.7),1,0)

If neither are what you are after, you will need to elaborate and explain your request better…
Steve

Reply | Quote

Hi Steve,

No you fully explained it the first paragraph of your last response. In my case I am only interested if any datapoint in the specified RANGE is beyond the spec limits 0 +/-0.7, the MIN and MAX gives me additional data of MIN and MAX which is good to know for future problems….

Thank you once again for your valuable input!!

Reply | Quote