+ Reply to Thread
Results 1 to 9 of 9

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

  1. 01-02-2020, 02:44 PM #1
    SixSeasons
    SixSeasons is offline
    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
    Reply With QuoteRegister To Reply
  2. 01-02-2020, 02:46 PM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,239

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 01-02-2020, 02:59 PM #3
    SixSeasons
    SixSeasons is offline
    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
    Reply With QuoteRegister To Reply
  4. 01-02-2020, 03:06 PM #4
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,496

    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
    Reply With QuoteRegister To Reply
  5. 01-02-2020, 03:24 PM #5
    SixSeasons
    SixSeasons is offline
    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.
    Reply With QuoteRegister To Reply
  6. 01-02-2020, 03:37 PM #6
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,496

    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).
    Reply With QuoteRegister To Reply
  7. 01-02-2020, 03:48 PM #7
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,496

    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
    Reply With QuoteRegister To Reply
  8. 01-02-2020, 04:06 PM #8
    SixSeasons
    SixSeasons is offline
    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!
    Reply With QuoteRegister To Reply
  9. 01-02-2020, 04:26 PM #9
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,496

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

    Glad that worked for you, AND thank you for the rep!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1