Formula deduction

[chriskenny]

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

 

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

 

[jtakw]

Hi,

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

Book1
	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
Sheet693

Cell Formulas
Range	Formula
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})))

 

[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

https://docs.google.com/spreadsheets/d/1qt5lks5grU3KyhUoNMWEk_vOoxix7KH4BueCAJ3K_4c/edit?usp=sharing

 

[chriskenny]

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

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

 

[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:

Book1
	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

Cell Formulas
Range	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})))

 

[igold]

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.

 

[chriskenny]

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

 

[chriskenny]

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:

	F	G	H	I	J	K	L	M

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]1300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]910[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1300[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1105[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]715[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]520[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]325[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]130[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]32000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet693​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2[/TH]
[TD="align: left"]=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})))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

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