# Formula deduction

#### chriskenny

##### New Member

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

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### igold

##### Well-known Member
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)))))))

#### jtakw

##### Well-known Member
Hi,

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

Last edited:

#### chriskenny

##### New Member
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

#### jtakw

##### Well-known Member
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:

#### igold

##### Well-known Member
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.

#### chriskenny

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

#### chriskenny

##### New Member
Thank you, worked perfect
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

</tbody>
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})))

</tbody>

<tbody>
</tbody>

#### jtakw

##### Well-known Member
You're welcome, welcome to the forum, and thanks for the feedback.