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!  Register To Reply

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  Register To Reply

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!   Register To Reply