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)))))))
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...it?usp=sharing
Thank you
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)))))))
igold
I'm a drinker with a coding problem...
All code is written with Excel 2010 - Please test all code on a backup copy of your data.
Hi,
If I understand correctly, use this in K2 copied down.
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
Worksheet Formulas
Cell 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})))
Last edited by jtakw; Jul 20th, 2019 at 05:36 PM.
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
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
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 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
Worksheet Formulas
Cell 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
I'm a drinker with a coding problem...
All code is written with Excel 2010 - Please test all code on a backup copy of your data.
Thank you, worked perfectly. Sorry if confused, trying to get my head around it all
You're welcome, welcome to the forum, and thanks for the feedback.
Like this thread? Share it with others