Multiple If (Lookup?) Statement

[Guest]

Can anyone help me write this formula?

The greater of D49 and I49 represents "Chargeable".
When Chargeable is:
1. Greater than D9 but less than E9, the value should be chargeable X D10
2. Greater than E9 but less than F9, the value should be chargeable X E10
3. Greater than F9 but less than G9, the value should be chargeable X E10
4. Greater than G9, the value should be chargeable XG10

When the Chargeable X D10 is less than C10, the value should be C10

 

[Roger Govier]

Hi Teri

I would use a helper cell to hold the chargeable value say J49 with a
formula of
=MAX(D49,I49)

Then
=IF(J49>G9,J49*G10,IF(J49>F9,J49*F10,IF(J49>E9,J49*E10,IF(J49>D9,MAX(C10,J49*D10),""))))

If you want the result all in one cell, then just substitute
MAX(D29,I49) for every occurrence of J49 in formula above.

 

[Guest]

This was the response from the other day.

=MAX(MAX(D49,I49)*LOOKUP(MAX(D49,I49),D9:G9,D10:G10),C10)

What was wrong with the result?

 

[vezerid]

One more formula using LOOKUP.

=LOOKUP(MAX(D49,I49),D9:G9,XD10:XG10)

HTH
Kostis Vezerides

 

[vezerid]

Oops, I disregarded the last condition:

When the Chargeable X D10 is less than C10, the value should be C10

Please ignore my formula.

Kostis Vezerides

 

[Guest]

Roger, thank you for your response, but when I follow your instructions
exactly, my cell remains blank. Using the audit tool, I traced all the
dependents and precedents and it looks fine. Any suggestions?

 

[Guest]

OK - you've changed your requirements from the other day.

=IF(MAX(D49,I49)*D10<C10,C10,MAX(D49,I49)*LOOKUP(MAX(D49,I49),D9:G9,D10:G10))

 

[Guest]

Hi Duke! Yes, when I ran the formula, it displayed a #N/A in the cell. This
one works perfectly!

Thank you EVERYBODY for your help