+ Reply to Thread
Results 1 to 3 of 3

Trying to calculate inefficient budget spend through complicated formula, please help! :)

  1. #1
    Registered User
    Join Date
    06-04-2013
    Location
    SF, CA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Question Trying to calculate inefficient budget spend through complicated formula, please help! :)

    I am looking to calculate in one cell how much of a budget in the month of May is spent inefficiently because the actual prices we are paying are greater than the target price.

    The formula I'm looking to create will take the following steps.
    1. Identify if actual prices in one column throughout the month are greater than the target price
    2. If the actual price is greater, I want to find the difference between the target price and the actual price
    3. Then I want to take the difference in the prices and multiple it by the # of units bought at that price
    4. Sum the amount spent
    4. Divide the sum by total budget spent

    Thank you!
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,789

    Re: Trying to calculate inefficient budget spend through complicated formula, please help!

    Hi and welcome to the forum

    You could do this with a helper column, which you can hide if you want (I used E), and use this, copied down...
    =IF(D4>$B$1,(D4-$B$1)*C4,"")
    Then in e35, USE THIS (iF i UNDERSTAND CORRECTLY)...
    =SUM(E4:E34)/B35
    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
    Registered User
    Join Date
    06-04-2013
    Location
    SF, CA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Trying to calculate inefficient budget spend through complicated formula, please help!

    Thanks for your help!

    And just wanted to confirm that it'd just be easier to use a helper column and not do it all in one cell?

    Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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