Results 1 to 6 of 6
  1. #1
    New Lounger
    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

  2. #2
    Uranium Lounger
    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)),(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.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    WS Lounge VIP sdckapr's Avatar
    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,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. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    New Lounger
    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 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. #6
    WS Lounge VIP sdckapr's Avatar
    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

Posting Permissions

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