IF help

[andrewb90]

Hello,

I have been trying to do an IF formaula, and can't quite seem to get it right. It is a nested IF so here is the first part:

=IF(H35=0,"-",

My value if false need to be an IF statement that takes M23,M26,M29,M32 and if any of those values (Which is time only) is greater than M20 then a positive value will be "Yes" and Negative will be "No"


Thanks

Andrew

 

[Snakehips]

Andrew,,

Try....

Excel 2007
	I
20	No
Sheet9

Cell Formulas
Range	Formula
I20	=IF(H35=0,"-",IF(OR(M23>M20,M26>M20,M29>M20,M32>M20),"Yes","No"))

Hope that helps.

 

[CharlesChuckieCharles]

This uses the OR function

<m20,m26<m20,m29<m20,m32<m20),"no","uncertain"))
=IF(OR(M23>M20,M26>M20,M29>M20,M32>M20),"Yes",IF(OR(M23<M20,M26<M20,M29<M20,M32<M20),"No","uncertain"))

Your explanation didn't really cover the detail so the above may not work quite the way you want</m20,m26<m20,m29<m20,m32<m20),"no","uncertain"))

 

[andrewb90]

works perfect! thank you!

 

[andrewb90]

Hi Snakehips,

I updated my formula and now I don't seem to be getting the correct result any longer. Here is my current formula:

=IF(H35=0,"-",(IF(OR($M$23>M35,$M$26>M35,$M$29>M35,$M$32>M35,$M$35>M35,$M$38>M35,$M$41>M35,$M$44>M35,$M$47>M35,$M$50>M35,$M$53>M35,$M$56>M35,$M$59>M35,$M$62>M35,$M$65>M35,$M$68>M35,$M$71>M35,$M$74>M35,$M$77>M35,$M$80>M35,$M$83>M35,$M$86>M35,$M$89>M35,$M$92>M35,$M$95>M35,$M$98>M35,$M$101>M35,$M$104>M35,$M$107>M35,$M$110>M35),"YES","NO")))

Now M35 has "-" as the default value. Now once H35 has a time typed into it, this cell (D35) evaluates to "NO" Once I type in a time value into M35 the value changes to "YES". However it is supposed to not change until another cell in the M column (every third cell as you can see) has a time entered that is greater than the time in M35.

Did I mess something up in this formula?

 

[Snakehips]

Andrew,

Now M35 has "-" as the default value.

Do you mean D35 ??

With your formula in D35 I would appear to get the correct results.....

With nothing in H35 D35 = "-"
D35 changes to NO when a value is entered in H35
D35 remains NO when value entered into M35
D35 only becomes YES when one or more other M cells is greater than M35

Is that not the case for you?

By the way, do you have any values in the intermediate cells in M, M24 , 25, 27, 28 etc ?

 

[andrewb90]

Ok,

D35 starts with a default value of "-"
When a time has been entered in H35 the value of D35 changes to "NO"
Once a time has been entered in M35 the value should stay "No"
Once any of the other cells in the M column (see my original post - every third row) have a time entered that exceed the time entered in M35 then the value of D35 should change to "YES"
Additonally, the cells in M column all have "-" as a default value (that can change to make the formula work if need be)

This formula, I am trying to run on every third row. The purpose is to check usage of product. H35 is where we enter the time the cooking has been completed. M35 is when the product starts getting served. When another time is entered in the M column, the value of D column for the original line should change to "YES" That says that the product has been fully used as another product has started being served.
There are 30 rows (every third row is used as the M column has three lines for splitting the product into thirds so the D:L section is merged together)
I need each row to be able to check if there is a time that is greater than the time in it's current row, and then if so the D column will evaluate to YES.

 

[andrewb90]

Andrew,

Now M35 has "-" as the default value.

Do you mean D35 ??

With your formula in D35 I would appear to get the correct results.....

With nothing in H35 D35 = "-"
D35 changes to NO when a value is entered in H35
D35 remains NO when value entered into M35
D35 only becomes YES when one or more other M cells is greater than M35

Is that not the case for you?

By the way, do you have any values in the intermediate cells in M, M24 , 25, 27, 28 etc ?

The intermediate cells have no value
Both M35 and D35 have "-" as a value.

 

[Snakehips]

The thing that is screwing your formula is the '-' in column M.
If you remove that and have blank cells when no time then your formula will work.

If you wish to retain the '-' in M then try this as an alternative in D35....

=IF(H35=0,"-",IF(SUMIF($M$23:$M$101,">"&M35)> 0,"YES","NO"))

I would now assume that you will have a similar formula in D of every third row?????

If so then use the above and just edit the H35 and M35 row numbers.
Hope that helps.

 

[andrewb90]

Perfect! Thank you very much!!!