Formula deduction
Results 1 to 10 of 10

Thread: Formula deduction

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    https://docs.google.com/spreadsheets...it?usp=sharing

    Thank you

  2. #2
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,325
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default 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)))))))
    ​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.


  3. #3
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default 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})))

    Last edited by jtakw; Jul 20th, 2019 at 05:36 PM.

  4. #4
    New Member
    Join Date
    Jun 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

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

  5. #5
    New Member
    Join Date
    Jun 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula deduction

    Quote Originally Posted by igold View Post
    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

  6. #6
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default 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})))


  7. #7
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,325
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula deduction

    Quote Originally Posted by chriskenny View Post
    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.
    ​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.


  8. #8
    New Member
    Join Date
    Jun 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face Re: Formula deduction

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

  9. #9
    New Member
    Join Date
    Jun 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula deduction

    Thank you, worked perfect
    Quote Originally Posted by jtakw View Post
    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})))

  10. #10
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Formula deduction

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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