Formula deduction

chriskenny

New Member
Joined
Jun 4, 2019
Messages
17
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,495
Office Version
365, 2010
Platform
Windows
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
Joined
Jun 29, 2014
Messages
5,146
Hi,

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">1300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">910</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">1300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1300</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">1300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1105</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">1300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">715</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">1300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">520</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">1300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">325</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">1300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">130</td><td style="text-align: right;;"></td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">1300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">7</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet693</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K2</th><td style="text-align:left">=IF(<font color="Blue">M2=0,F2,IF(<font color="Red">M2>6,0,F2-F2*LOOKUP(<font color="Green">M2,{1,2,3,4,5,6},{0.15,0.3,0.45,0.6,0.75,0.9}</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

chriskenny

New Member
Joined
Jun 4, 2019
Messages
17
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">1300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">910</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">1300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1300</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">1300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1105</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">1300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">715</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">1300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">520</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">1300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">325</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">1300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">130</td><td style="text-align: right;;"></td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">1300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">32000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet693</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K2</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">M2="",M2>6</font>),0,IF(<font color="Red">M2=0,F2,F2-F2*LOOKUP(<font color="Green">M2,{1,2,3,4,5,6},{0.15,0.3,0.45,0.6,0.75,0.9}</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,495
Office Version
365, 2010
Platform
Windows
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

New Member
Joined
Jun 4, 2019
Messages
17
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>
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
You're welcome, welcome to the forum, and thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,848
Messages
5,489,236
Members
407,682
Latest member
gmb2521

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top