+ Reply to Thread
Results 1 to 9 of 9

Need help with multiplying a qualifying value by a fixed number!

  1. #1
    Registered User
    Join Date
    01-02-2020
    Location
    St. Louis
    MS-Off Ver
    MS Office Professionial Plus 2016
    Posts
    4

    Need help with multiplying a qualifying value by a fixed number!

    Hello!

    As the title states, I am trying to learn a formula for a tracker I have built for sales. I have one column that has a drop-down selection. For any row that his this drop-down labeled as "Life," I'd like to multiply the dollar amount by a fixed number (in this case, 2.4).

    I have attached the portion of the sheet that I am dealing with. As you can see, Life has been selected in the drop-down (Type of Policy), and the Premium amount is filled in with an example dollar amount. The "Points Total" column should read '48' for the first (20*2.4), and 67.20 for the second (28*2.4).

    Thank you for all your help!
    Attached Images Attached Images

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,092

    Re: Need help with multiplying a qualifying value by a fixed number!

    Welcome to the forum.

    Please attach the workbook - instructions at the top of the page.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    01-02-2020
    Location
    St. Louis
    MS-Off Ver
    MS Office Professionial Plus 2016
    Posts
    4

    Re: Need help with multiplying a qualifying value by a fixed number!

    Thank you for the correction. Attached is the test sample I had previously in screenshot format.
    Attached Files Attached Files

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,931

    Re: Need help with multiplying a qualifying value by a fixed number!

    is this what you want? =IF(H10="life",P10*2.4,"") dragged down.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  5. #5
    Registered User
    Join Date
    01-02-2020
    Location
    St. Louis
    MS-Off Ver
    MS Office Professionial Plus 2016
    Posts
    4

    Re: Need help with multiplying a qualifying value by a fixed number!

    Hi Sambo,

    Thanks for the response. It looks like I'm getting an error somewhere when I do that. I get the #VALUE! error. I also tried, =IF(H10:I43, "LIFE", P10:Q43*2.4, "") , =IF(H10:I43,"LIFE",P10:Q43*2.4,"") , =IF(H10:I43, "LIFE", P10:Q43*2.4).

    All give me the #VALUE! error.

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,931

    Re: Need help with multiplying a qualifying value by a fixed number!

    first, I just noticed this but you are using merged cells, it is best to never use merged cells.
    second, are you looking to do this for a column or a single cell row by row? If for a column then I'd use =SUMIF(H10:H23,"life",P10:P23)*2.4
    One problem in what you wrote in post #5 is that you have ranges in your formulas H10:I43 when if you click on "life" in row 10 and look at the cell address at the top of the workbook across from the formula line you will see it says you are actually in H10, not I10.
    But my formula will give you a row by row total. Paste it for example in U10 and it will return 48 (20 in P10 times 2.4).

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,931

    Re: Need help with multiplying a qualifying value by a fixed number!

    attached is your workbook with the formulas in it. Sheet1 is what you had and in cells R10 and R11 is the row by row formula with the formula I gave you. In U10 is the sumif formula I gave you.

    I also added another tab with fewer merged cells to show you how it could be done without all the merged cells and both formulas.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    01-02-2020
    Location
    St. Louis
    MS-Off Ver
    MS Office Professionial Plus 2016
    Posts
    4

    Re: Need help with multiplying a qualifying value by a fixed number!

    That worked! Thank you!

  9. #9
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,931

    Re: Need help with multiplying a qualifying value by a fixed number!

    Glad that worked for you, AND thank you for the rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] multiplying a number in a cell containing both a number and text
    By Skyfawn in forum Excel General
    Replies: 3
    Last Post: 10-23-2017, 09:48 AM
  2. [SOLVED] Fixed Cell Reference Minus a Fixed Number
    By juddykong in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-03-2013, 03:25 PM
  3. [SOLVED] VLOOKUP combined with multiplying a fixed cell
    By Bax in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-18-2012, 11:18 AM
  4. Multiplying an Entire Range by a fixed number
    By ppisklak in forum Excel General
    Replies: 2
    Last Post: 12-15-2011, 01:25 PM
  5. Multiplying a cell number by a user input number
    By awalsh in forum Excel General
    Replies: 3
    Last Post: 08-26-2010, 09:42 AM
  6. Multiplying cells by various fixed values...
    By ChrisMattock in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-12-2007, 09:16 AM
  7. multiplying a cell by a set number
    By gunny86 in forum Excel General
    Replies: 2
    Last Post: 09-07-2005, 03:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1