1. ## Complex Formula (XP)

I need to simulate some scenarios and need to use some complex formula.

Attached is the spreadsheet on the scenario.

The cell highlighted in yellow is the one which I need to insert a formula to get the amount.

The cells highlighted in blue is the condition to be satisfied. Let say in cell C38 then the correct figure is \$2,655.63, ie (\$210,000 - \$185,209)*1% + \$185,209*1.3%.

What formula should be used so that if the first condition is not met, then it will will go to the second condition, if not the third condition. If the third condition is not met then C38 should be 0.

Looking forward to your help. Thank you

2. ## Re: Complex Formula (XP)

I think you'll need to give us more examples of what the results should be. If I modify your worksheet to collapse the two tables like this:
<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>30</td><td>C rate (below Target 1)=</td><td align=center> </td><td align=right> </td><td align=right>0</td><td align=center>31</td><td>C rate (below Target 2)=</td><td align=center> </td><td align=right>165300</td><td align=right>0.005</td><td align=center>32</td><td>C rate (below Target 3)=</td><td align=center> </td><td align=right>178156.667</td><td align=right>=D28</td><td align=center>33</td><td>C rate (above Target 3)=</td><td align=center> </td><td align=right>185209.467</td><td align=right>0.01</td></table>Then in cell C38

=IF(ISNUMBER(VLOOKUP(C36,\$C\$30:\$D\$33,1)),(C36-VLOOKUP(C36,\$C\$30:\$D\$33,1))*VLOOKUP(C36,\$C\$30:\$D\$3 3,2)+VLOOKUP(C36,\$C\$30:\$D\$33,1)*OFFSET(\$D\$30,MATCH (C36,\$C\$30:\$C\$33)-2,0),)

does what you want, but not sure what the result should be for Cell D38. (I suspect my formula doesn't return the right answer.)

3. ## Re: Complex Formula (XP)

It seems to me that only condition 4 is met :
[C rate(210,000) is above Target 3 185209 so I would think the answer is210,000*1%, it does not meet conditon 3 the Crate is > target 3]

If you were going to use multiple conditonal parts why not use them all:
(210,000-185209)*1% + (185209-178157)*1.3% + (178157-165300)*0.5% + 165300*0%

I don't see how to work it if you use "some of the conditions" as you state (you only used the 1% from 4 and 1.3 from 3)
What if the value is between 1 & 2. It it meets conditions 2 and 3 (<2 and <3) and if it is <cond 1 it is less than all 1,2,3.

Could you elaborate on what C38 is supposed to do?

Steve

4. ## Re: Complex Formula (XP)

A followup question:
Could you detail how you would calculate the value in C38 if C36 were each of the following?
85,000
165,300
170,000
178,157
180,000
185,209

I think this would give us an idea of what you are after

Steve

5. ## Re: Complex Formula (XP)

Hi Steve,

I've re-do my question in the attached worksheet. Hope that this will give you a clearer picture of what I'm asking.

Hope to hear from you soon.

Thank you <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Min

6. ## Re: Complex Formula (XP)

If I understand correctly: in your original sheet in C38 enter in this formula:
=IF(C36>\$C\$17,(C36-\$C\$17)*\$D\$33+\$C\$17*D32,C36*INDEX(\$D\$30:\$D\$32,MATCH (C36,\$C\$14:\$C\$16,1)))

It can then be copied to D38, E38, etc

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•