Formula deduction

chriskenny

New Member
Joined
Jun 4, 2019
Messages
21
I have created a spreadsheet in google sheets and I am having problems with the following formula. Please see attached link to the google sheet.

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

This is the link to google sheet
https://docs.google.com/spreadsheets/d/1qt5lks5grU3KyhUoNMWEk_vOoxix7KH4BueCAJ3K_4c/edit?usp=sharing

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.
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)))))))
 
Upvote 0
Hi,

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


Book1
FGHIJKLM
213009102
3130013000
4130011051
513007153
613005204
713003255
813001306
9130007
Sheet693
Cell Formulas
RangeFormula
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})))
 
Last edited:
Upvote 0
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:


Book1
FGHIJKLM
213009102
3130013000
4130011051
513007153
613005204
713003255
813001306
9130007
10320000
Sheet693
Cell Formulas
RangeFormula
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})))
 
Upvote 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

https://docs.google.com/spreadsheets...it?usp=sharing

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.
 
Upvote 0
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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})))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
You're welcome, welcome to the forum, and thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top