Results 1 to 6 of 6
Thread: Complex Formula (XP)

20040503, 23:45 #1
 Join Date
 May 2003
 Location
 South Australia, Singapore
 Posts
 22
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20040504, 02:46 #2
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
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)),(C36VLOOKUP(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.)John ... I float in liquid gardens
UTC 7ąDS

20040504, 09:38 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Complex Formula (XP)
I don't understand your "conditions" and your "solution"
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,000185209)*1% + (185209178157)*1.3% + (178157165300)*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

20040504, 10:23 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20040504, 23:52 #5
 Join Date
 May 2003
 Location
 South Australia, Singapore
 Posts
 22
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Formula (XP)
Hi Steve,
I've redo 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

20040505, 09:35 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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