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

1. ## 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!

2. ## 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

3. ## 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!

##### Users Browsing this Thread

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

#### 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