Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula Help (2000)

    Hello, I am having problems structuring a formula. I have a number of threshold figures that determine what calculation I need to do. For instance assume I have threshold A and theshold B, if someone's target is above threshold A but below theshold B then I need to calculate a figure for pay below A and a differnet figure for the element between A and B. Furthermore idfsomeone exceeds B also I need to caclulate for below A, between A and B and above B.

    Many thanks Darren.

  2. #2
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help (2000)

    I'm not really sure what you're trying to do - it sounds like you have more than one calculation to do in some instances, in which case we're talking about more than one formula (in more than one result cell)? Perhaps it would be a good idea to attach an example spreadsheet, showing the layout of your data and where you want the results to appear.
    Waggers
    If at first you do succeed, you've probably missed something.

  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: Formula Help (2000)

    It sounds like you want something like a non-exact vlookup (or Hlookup) formula (a tax table is an example). Could you elaborate on what you have and want and we can provide more details

    Steve

  4. #4
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help (2000)

    Thanks guys. The situation is that where a target has been achieved that is =< 33,540 I need to multiply by 0.67, if it isf> 33,540 but <= 38,335 then upto 33,540 by 0.67 and 0.77 for between and finally for target over 37,335 I need 0.67 for bit upto 33,540, 0.77 for 33,540 to 38,335 and 0.59 above 38,335. Does this help any more?

    Thanks again darren.

  5. #5
    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: Formula Help (2000)

    I am not sure I completely understand, but here is a crack at it.

    Does this setup do what you want?
    Enter values in A1:B3 (your cutoff values) and C1
    In C2 and C3 enter the formula listed

    In F1 enter the formula listed

    In E1, enter the value to calculate from : the result is in F1
    Steve

    <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>E</td><td align=center>F</td><tr><td align=center>1</td><td align=right>0</td><td align=right>0.67</td><td align=right>0</td><td align=right>

  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: Formula Help (2000)

    Or perhaps this lookup table is more appropriate:
    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>1</td><td align=right>0</td><td align=right>0.67</td><td align=right>0</td><td align=center>2</td><td align=right>33540</td><td align=right>0.77</td><td>=C1+B1*(A2-A1)</td><td align=center>3</td><td align=right>38355</td><td align=right>0.59</td><td>=C2+B2*(A3-A2)</td></table>

    Steve

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help (2000)

    Does the formula below give you what you want:

    <code>
    =IF(A1<=33540,A1*0.67,IF(A1<=38335,33540*0.67+(A1-33540)*0.77,33540*0.67+(38335-33540)*0.77+(A1-38335)*0.59))
    </code>
    Legare Coleman

Posting Permissions

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