Thread: Formula deduction Thanks:  1 Post #5312642 (1) Likes:  3 Post #5312621 (1)Post #5312642 (1)Post #5312619 (1)

1. Formula deduction

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

Thank you  Reply With Quote

2. Re: Formula deduction

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)))))))  Reply With Quote

3. Re: Formula deduction

Hi,

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

FGHIJKLM
213009102
3130013000
4130011051
513007153
613005204
713003255
813001306
9130007

Sheet693

Worksheet Formulas
CellFormula
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})))  Reply With Quote

4. Re: Formula deduction

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  Reply With Quote

5. Re: Formula deduction Originally Posted by 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)))))))
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  Reply With Quote

6. Re: Formula deduction

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

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})))  Reply With Quote

7. Re: Formula deduction Originally Posted by 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

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.  Reply With Quote

8. Re: Formula deduction

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

9. Re: Formula deduction

Thank you, worked perfect Originally Posted by 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:

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})))  Reply With Quote

10. Re: Formula deduction

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

User Tag List

Tags for this Thread

column, figure, formula, google, sheet  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•