# Thread: Formula deduction Thanks:  1 Post #5312642 (1) Likes:  3 Post #5312621 (1)Post #5312642 (1)Post #5312619 (1)

1. ## Formula deduction

In column K the formula works out the figure after a number is entered in column M but I need the figure in column K (after calculation) to also be deducted from column F so for example column f is 1300 minus column K 390 = 990

Thank you

2. ## Re: Formula deduction

How about the below formula in column K:

=F2-IF(M2=0,F2,IF(M2=1,F2*0.15,IF(M2=2,F2*0.3,IF(M2=3,F2*0.45,IF(M2=4,F2*0.6,IF(M2=5,F2*0.75,IF(M2=6,F2*0.9,0)))))))

3. ## Re: Formula deduction

Hi,

If I understand correctly, use this in K2 copied down.

FGHIJKLM
213009102
3130013000
4130011051
513007153
613005204
713003255
813001306
9130007

Sheet693

Worksheet Formulas
CellFormula
K2=IF(M2=0,F2,IF(M2>6,0,F2-F2*LOOKUP(M2,{1,2,3,4,5,6},{0.15,0.3,0.45,0.6,0.75,0.9})))

4. ## Re: Formula deduction

Thank you for your assistance and it works for the first row but when I drag down to the second row it is applying a refund when it is not due

5. ## Re: Formula deduction

Originally Posted by igold
How about the below formula in column K:

=F2-IF(M2=0,F2,IF(M2=1,F2*0.15,IF(M2=2,F2*0.3,IF(M2=3,F2*0.45,IF(M2=4,F2*0.6,IF(M2=5,F2*0.75,IF(M2=6,F2*0.9,0)))))))
Thank you for your assistance and it works for the first row but when I drag down to the second row it is applying a refund when it is not due

6. ## Re: Formula deduction

Doesn't look like you've tried my formula in Post # 3 yet, but it seems like you want K2 to show 0 when M2 is Blank ( which was not in the description in your OP ), modified below, is this what you mean:

FGHIJKLM
213009102
3130013000
4130011051
513007153
613005204
713003255
813001306
9130007
10320000

Sheet693

Worksheet Formulas
CellFormula
K2=IF(OR(M2="",M2>6),0,IF(M2=0,F2,F2-F2*LOOKUP(M2,{1,2,3,4,5,6},{0.15,0.3,0.45,0.6,0.75,0.9})))

7. ## Re: Formula deduction

Originally Posted by chriskenny
Thank you for your assistance and it works for the first row but when I drag down to the second row it is applying a refund when it is not due

Then I would say that your original formula needs to be worked on. Your request was to subtract your formula from Column F. That is exactly what the formula I gave you does. You may need to add another If/Then condition.

8. ## Re: Formula deduction

Thank you, worked perfectly. Sorry if confused, trying to get my head around it all

9. ## Re: Formula deduction

Thank you, worked perfect
Originally Posted by jtakw
Doesn't look like you've tried my formula in Post # 3 yet, but it seems like you want K2 to show 0 when M2 is Blank ( which was not in the description in your OP ), modified below, is this what you mean:

F G H I J K L M
2 1300 910 2
3 1300 1300 0
4 1300 1105 1
5 1300 715 3
6 1300 520 4
7 1300 325 5
8 1300 130 6
9 1300 0 7
10 32000 0
Sheet693

Worksheet Formulas
Cell Formula
K2 =IF(OR(M2="",M2>6),0,IF(M2=0,F2,F2-F2*LOOKUP(M2,{1,2,3,4,5,6},{0.15,0.3,0.45,0.6,0.75,0.9})))

10. ## Re: Formula deduction

You're welcome, welcome to the forum, and thanks for the feedback.