Multiple If (Lookup?) Statement

T

Teri

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
 
R

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.
 
D

Duke Carey

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?
 
V

vezerid

One more formula using LOOKUP.

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

HTH
Kostis Vezerides
 
V

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
 
T

Teri

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?
 
D

Duke Carey

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))
 
T

Teri

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 :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top