# 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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

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

1,102,226
Messages
5,485,512
Members
407,503
Latest member
Hereheis

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...