1. 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. 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.

3. 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. 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. 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. 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. 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>

Posting Permissions

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