Thanks:  0
Likes:  0

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

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))))

3. ## Re: Clever formula

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

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))))`

5. ## Re: Clever formula

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

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

7. ## Re: Clever formula

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

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%

```

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

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

## 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
•