 Thanks:  0 Likes:  0

# Thread: Clever formula

1. ## Clever formula

Hello

Can anyone help me write a single formula that will solve for the total fee to be deducted given this fee card:

First 100 - 1.25%
Next 200 - 1.00%
Next 400 - 0.75%
Balance above 700 - 0.50%

I have only been able to do it so far using a range of cells.

Many thanks
Tom  Reply With Quote

2. ## Re: Clever formula

something along the lines of

=if(a1<101,(a1*1.25)/100,if(a1<301,(a1*1.00)/100,if(a1<701,(a1*0.75)/100,(a1*0.5)/100))))  Reply With Quote

3. ## Re: Clever formula

Or maybe
=A1*LOOKUP(A1,{100,200,400,700},{0.0125,0.01,0.0075,0.005})  Reply With Quote

4. ## Re: Clever formula

I'd probably say:
Code:
`=if(a1<=100,a1*1.25%,if(a1<=300,(a1-100)*1%+1.25,if(a1<=700,(a1-300)*0.75%+3.25,(a1-700*0.5%+6.25))))`  Reply With Quote

5. ## Re: Clever formula

Think i made a mistake!

=A1*LOOKUP(A1,{0,101,301,701},{0.0125,0.01,0.0075,0.005})  Reply With Quote

6. ## Re: Clever formula Originally Posted by SteveO59L something along the lines of

=if(a1<101,(a1*1.25)/100,if(a1<301,(a1*1.00)/100,if(a1<701,(a1*0.75)/100,(a1*0.5)/100))))

Thanks
Not sure that this takes into account the Income tax style tiers.

I have done this but it is returning #VALUE!

PHP Code:
``` =IF(A6>=C3,((A6-C3)*0.005)+62500),IF(A6>=C2,((A6-C2)*0.0075)+32500),IF(A6>=C1,((A6-C1)*0.01)+12500),IF(A6<=C1,A6*0.0125,"ERROR")  ```
That is with different numbers.

Thanks a lot  Reply With Quote

7. ## Re: Clever formula

Did you try
=A6*LOOKUP(A6,{0,101,301,701},{0.0125,0.01,0.0075,0.005})  Reply With Quote

8. ## Re: Clever formula

Or with a table in D1:E4

=A6*LOOKUP(A6,D1:D4,E1:E4)

Code:
```0
1.25%

101
1.00%

301
1%

701
0.50%

```  Reply With Quote

9. ## Re: Clever formula Originally Posted by gaz_chops Or with a table in D1:E4

=A6*LOOKUP(A6,D1:D4,E1:E4)

Code:
```
1.25%

101
1.00%

301
1%

701
0.50%

```
Thanks

I'm not sure that the LOOKUP takes into account the fact that the first 100 is charged at 1.25% and the next x amount is charged at 1% (up until 300), and so on.

Thanks again  Reply With Quote

10. ## Re: Clever formula

Ah got you. Originally Posted by kellta Thanks

I'm not sure that the LOOKUP takes into account the fact that the first 100 is charged at 1.25% and the next x amount is charged at 1% (up until 300), and so on.

Thanks again  Reply With Quote

## User Tag List

#### Posting Permissions

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