If statement based on cell data, if falls in range.

**finch82** · 10-27-2011, 02:30 PM

Hello,

I have attached a file that im having a little problem with, what i need to do is the following;

I have worked out total hours between 2 dates, includign subtracting weekends. This is shown in column F.

From this I need to know if the hours totalled fall within a certain criteria parameters. These are represented by the Priority code in column A, and the parameters are as follows;

100 = 24hrs
200 = 48hrs
250 = 120hrs
400 = 960hrs

What I want to happen in column's in G:J is if the criteria is met then a '1' is placed into the revelant cell and a '0' is placed in the others. In the attached I shown an example of how it should look.

There should only ever be a '1' in the matching priority code and column cell if the hours is with range.

Please help.

Chris.

**JBeaucaire** · 10-27-2011, 03:43 PM

The answer should be this in G2, then copied down/across:

=--($A2=G$1)

Unfortunately you have some text strings (column A) that look like numbers, but aren't. So this is what you'll have to use unless you fix the data to all be actual numbers:

=--($A2+0=G$1+0)

**finch82** · 10-27-2011, 03:54 PM

This formula seems to put a '1' in the matched, but i should only have a 1 if the criteria is met, an example of this not working would be cell H4, this is a priority 200 therefore should have a value in column F of less then 48, but its value is actually is 158.11. So there should be '0' in all boxes G:J

**ChemistB** · 10-27-2011, 04:11 PM

Try this

=--(LOOKUP($F2,{0,24,48,120,960},{100,200,250,400,0})=G$1)

Not following your logic with row 4. Shouldn't a 158 hr give a 400 code (between 120 and 960)?