Using multiple if and statements

[bryden2008]

Hi,

I am using this -

=IF(AND(R118="Y",SUM(O97:O116)>139.5),(SUM(O97:O116)*G90)/2,H90/2,IF(AND(R118="",SUM(O97:O116)>139.5),SUM(O97:O116)*G90,H90))

In cell O118 - What am I missing?

Thank you!

[mcmahobt]

Your problem is you have incorrect syntax within your IF statement. Broken out by color:

=IF(AND(R118="Y",SUM(O97:O116)>139.5),(SUM(O97:O116)*G90)/2,H90/2,IF(AND(R118="",SUM(O97:O116)>139.5),SUM(O97:O116)*G90,H90))

The green is your initial AND statement to test against. The blue is your result if the AND statement returns TRUE for both criteria (both "Y" and >139.5). The orange is the result if the AND statement is FALSE. The formula is returning a #VALUE error because you have a trailing IF statement (which I believe you mean to have run instead for the FALSE condition) which is outside the original syntax for your initial IF statement.

To summarize, your blue-colored or orange-colored result is what is throwing off your expected result.

[bryden2008]

Correct -

The result should be - if there is a Y in R118, then do either SUM(O97:O116)>139.5),(SUM(O97:O116)*G90)/2,H90/2)
If there is a blank in R118, then do SUM(O97:O116)>139.5),(SUM(O97:O116)*G90),H90)

Possible to do both?

[mcmahobt]

Need to edit my previous post, the formula I posted will not return the correct value based on your logic.

The question then is two fold - you say

if there is a Y in R118, then do either SUM(O97:O116)>139.5),(SUM(O97:O116)*G90)/2,H90/2)

That would be an individual IF statement right there. The IF statement would stop. Does this supersede the need for a result of R118 is blank?

[AliGW]

Bryden - you would be better off explaining in WORDS what you want the formula to do.

[bryden2008]

Thank you both!

mcmahobt and Ali...I think this is it:

If there is a Y in R118, then take the sum of O97:O116 times G90/2 or H90/2....which ever is greater.
If R118 is blank, then take the sum of O97:O116 times G90 or H90....which ever is greater.

[AliGW]

Try this:

=IF(R118="Y",SUM(O97:O116)*MAX(G90/2,H90/2),SUM(O97:O116)*MAX(G90,H90))

[JohnTopley]

Try

=IF(R118="Y",SUM(O97:O116)*MAX(G90/2,H90/2),SUM(O97:O116)*MAX(G90,H90))

[bryden2008]

Thank you!
It is returning 5921.605096

With a Y there, the Service Charge should be 42.45

[mcmahobt]

Note that ALIGW's formula implies that anything other than a "Y" in R118 will return a TRUE value - not just an empty cell (""). Not incorrect logic by any means, just a point of distinction.

[AliGW]

Until Bryden defines any further requirement that he has not yet divulged, it does what he asks, but I expect you are right to point it out, as I am sure he is going to come back again and tell us that it needs to do something else if R118 is not blank or Y.

[JohnTopley]

The value in G90 is a % (10%) but H90 is value $13.95 : this suggests the test is wrong

it should be

=IF(R118="Y",MAX(SUM(O97:O116)*G90/2,H90/2),MAX(SUM(O97:O116)*G90,H90))

[davsth]

=IF(R118="Y",0.5,1)*SUM(O97:O116)*MAX(G90,h90) is a little shorter and I think does the same !

[bryden2008]

You are correct in this logic, except that G90 is a % (10%), and H90 is a number (13.95).
For a Y, we want it to take the sum of the totals times .10 divided by 2 or return the number (13.95) divided by 2.....which ever result is would be greater.
And yes, I need to the same outcome for when there is no Y, but not divided by 2.

Helpful - in Q118 "EOW SC" means "What would the service charge be if we serviced if every other week...."

[JohnTopley]

Formula in post #12 returns 42.45

[bryden2008]

BULLSEYE!
Thank you John!

[bryden2008]

The only issue is that H90 and G90 can be changed by the input (user).
And this formula came back with 5921.605096 too.

[bryden2008]

Thank you all!