Results 1 to 7 of 7
Thread: Formula Help (2000)

20060523, 08:40 #1
 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.

20060523, 09:37 #2
 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.

20060523, 10:14 #3
 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 nonexact 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

20060523, 10:38 #4
 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.

20060523, 11:09 #5
 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>

20060523, 11:15 #6
 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*(A2A1)</td><td align=center>3</td><td align=right>38355</td><td align=right>0.59</td><td>=C2+B2*(A3A2)</td></table>
Steve

20060523, 14:46 #7
 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+(A133540)*0.77,33540*0.67+(3833533540)*0.77+(A138335)*0.59))
</code>Legare Coleman