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

Hi Teri

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

Then
=IF(J49G9,J49*G10,IF(J49F9,J49*F10,IF(J49E9,J49 *E10,IF(J49D9,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.

--
Regards

Roger Govier


"Teri" wrote in message
...
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

This was the response from the other day.

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

What was wrong with the result?

"Teri" wrote:

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

One more formula using LOOKUP.

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

HTH
Kostis Vezerides

On Jan 31, 8:53 pm, Teri wrote:
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

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

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?

"Roger Govier" wrote:

Hi Teri

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

Then
=IF(J49G9,J49*G10,IF(J49F9,J49*F10,IF(J49E9,J49 *E10,IF(J49D9,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.

--
Regards

Roger Govier


"Teri" wrote in message
...
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

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

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



"Teri" wrote:

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

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

"Duke Carey" wrote:

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

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



"Teri" wrote:

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