Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Choosing multiplying a number selected from a table

  1. #1
    Acolyte mkellycole's Avatar
    Join Date
    Nov 2015
    Posts
    24
    Articles
    0
    Excel Version
    2016

    Choosing multiplying a number selected from a table



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am trying to set a rating of 1-3 using a table. The multiply this rating to deduct or add a balance based on the rating to come up with a total paid. This work on only one of the numbers in the table.

    =SUM(R5*Y4,CHOOSE(T5,"1","2","3"))

    The formula returns (848) but if I change it to a 2, it errors. I have a whole department with a rating of 1,2 or 3.


    R1
    Total Comp S1 EXTRAS/ T1
    MBP Rating
    U1
    TOTAL
    DEDUCTS PAID
    $ 1,702 $ (848) 3 $ 754


    X1
    Rate Y1
    Bonus%
    1 (0.50)
    2 1.00
    3 0.25

  2. #2
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,615
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Please post a sample sheet. Thx

  3. #3
    Acolyte mkellycole's Avatar
    Join Date
    Nov 2015
    Posts
    24
    Articles
    0
    Excel Version
    2016

    File

    Quote Originally Posted by Pecoflyer View Post
    Please post a sample sheet. Thx
    Trying to calculate T with a changing number in U from table A7:B11
    Attached Files Attached Files

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Hello
    Your upload is not helping, because you have an invalid formula in cell M4 because it has a recursive error. In this instance, you are using M4 itself as part of the calculation of M4 which means that a final result cannot be determined. You can enable recursive calcuilating, and this might be responsible for the problems your having. Can you post the correct formula that should be in M4?

  5. #5
    Acolyte mkellycole's Avatar
    Join Date
    Nov 2015
    Posts
    24
    Articles
    0
    Excel Version
    2016
    New file. The only formula I need is in T4
    Attached Files Attached Files

  6. #6
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by mkellycole View Post
    The only formula I need is in T4
    Try
    Code:
    =S4*VLOOKUP($U$4;$A$9:$B$11;2;FALSE)
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  7. #7
    Acolyte mkellycole's Avatar
    Join Date
    Nov 2015
    Posts
    24
    Articles
    0
    Excel Version
    2016
    This is not working. It is returning an error.

  8. #8
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by mkellycole View Post
    This is not working. It is returning an error.
    You may need to use a comma instead of a semicolon
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  9. #9
    Acolyte mkellycole's Avatar
    Join Date
    Nov 2015
    Posts
    24
    Articles
    0
    Excel Version
    2016
    Here is what I am trying to do:

    Open the formula Problem Sheet.

    I want to put take the number used in D5 (1-3), Look up the table in H4:h6, select the value the number represents in i4:i6 (-.5, 1 or .25), multiply what it returns by B5 to get a reduction or increase in the value in B5 and put the result in C5 (REF#).
    Attached Files Attached Files

  10. #10
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,615
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Try =B5*(1-VLOOKUP($D$5,$H$4:$I$6,2,0)/100)

Page 1 of 2 1 2 LastLast

Posting Permissions

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