If values in specified cells fall within a numerical range

**joerob88** · 11-09-2009, 10:25 AM

Can this formula be written. If the cells from B1 to E1 contain numbers between 29 & 42 then A1 = to 1 or n/a

Thank you.

**DonkeyOte** · 11-09-2009, 10:28 AM

All of them or one of them ?

All: =IF(SUM(COUNTIF(B1:E1,{">=29",">42"})*{1,-1})=COLUMNS(B1:E1),1,"N/A")

One: =IF(SUM(COUNTIF(B1:E1,{">=29",">42"})*{1,-1}),1,"N/A")

**joerob88** · 11-09-2009, 10:51 AM

Thank you, however that did not seem to work. when there were values between 0 & 28 in the four cells to the right of A1, the formula still returned a "N/A". To recap, should the value contained in any cell of the four cells to right of A1 contain a value between 29 & 42 then i need to have a the number 1 returned in A1. If any of the numbers in cells B1 to E1 do not fall between 29 & 42, then i want A1 to remain blank.

**DonkeyOte** · 11-09-2009, 11:01 AM

See the formula provided earlier for One: (rather than All) - your initial post was quite vague so I provided both.

**joerob88** · 11-09-2009, 11:19 AM

That worked, thank you! However i am now finding that it may be better if i had "n/a" returned in cell A1, when ever the four cells to the right contain a number between 29 & 42. If there are any other numbers outside of this range then i need A1 to remain blank. Can this be done?

Thank you again.

**DonkeyOte** · 11-09-2009, 12:21 PM

If you're now saying A1 should return n/a if all four values are within the specified range else blank then alter the earlier All example accordingly by amending the respective TRUE/FALSE outputs:

Please Login or Register  to view this content.

**joerob88** · 11-10-2009, 09:37 AM

Yes that did it, Thank you, very much!
One thing, could you explain the formula to me?

Thanks again.

**DonkeyOte** · 11-10-2009, 09:42 AM

The formula conducts two COUNTIF's - the first counts how many numbers in range >= 29 and the second how many exceed 42.
The result of the first count is multiplied by 1 and the second by -1
The two results are added together and then compared to the number of columns in the range that was used.
If the combined counts match the column count then you know all values are within the specified parameters, if not 1 or more must reside outside.
Based on the TRUE/FALSE output of that calculation the appropriate string is returned.