+ Reply to Thread
Results 1 to 5 of 5

Formula for a cell value with a multiplier

  1. #1
    Registered User
    Join Date
    01-02-2013
    Location
    Orem, UT
    MS-Off Ver
    Excel 2010
    Posts
    58

    Formula for a cell value with a multiplier

    I'm fairly new to Excel so here goes. So I'm in sales and we offer a certain budget worth of free product per customer when we set the customer up on our services. Each product has a point value associated with it. So we are allowed to give up to 8.5 points worth of free product per customer. The multiplier is for every .5 point of product above the 8.5 point threshold we end up giving for free, then the sales rep has to pay $15.00. On the flip side, for every .5 point below the 8.5 point threshold we gain $15.00.

    So the question is what would the formula look like to make the numbers read correctly in the cells? My goal would be to simply enter in the total amount of points given shown in Column A and in Column B have the cell auto populate the dollar amount +/-. The key factor is 8.5 points is the middle. Anything above I have to pay, anything below I make money. So what's the formula to make Column B read correctly based on the point value entered in Column A?

    Again I'm kinda of new to Excel so I hope my question makes sense. I appreciate your help!

    Example:
    A B
    1 Free Points Point Profit +/-
    2 9.5 ($30.00)
    3 8.0 $15.00
    4 7.5 $30.00
    5 9.0 ($15.00)

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,959

    Re: Formula for a cell value with a multiplier

    try this, copied down. format as currency...
    =IF(A2=8.5,0,(8.5-A2)/0.5*15)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,959

    Re: Formula for a cell value with a multiplier

    try this, copied down. format as currency...
    =IF(A2=8.5,0,(8.5-A2)/0.5*15)

  4. #4
    Registered User
    Join Date
    01-02-2013
    Location
    Orem, UT
    MS-Off Ver
    Excel 2010
    Posts
    58

    Re: Formula for a cell value with a multiplier

    It works perfectly except if there is no point amount entered, then it shows a dollar amount of $255 for some reason.

  5. #5
    Registered User
    Join Date
    01-02-2013
    Location
    Orem, UT
    MS-Off Ver
    Excel 2010
    Posts
    58

    Re: Formula for a cell value with a multiplier

    Sorry, I get it now. The $255.00 that show if you put no point value in the first column is the amount you would make if didn;t give the customer any new product. I get it! Thank you so much!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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