Formula Issue With Blank Cells (Product function)

hecgroups,

Give this a try:

Please Login or Register  to view this content.

I'd have to see your data, or an example file that is experiencing the issue. And if you want to increase by 25%, why not just multiply by 1.25? Also, you could take out that ",1" within the product formula, it's not doing anything.

The formula will increase by 25% correctly with cell B24, but not with B14:B21 because you are multiplying then adding a static number. As a result, it won't be exactly *1.25*1.25, it will be ((#+#)*1.25+#)*1.25. I don't know what you're trying to do with your formula because I have no background in what this sheet seems to be about. If you were to calculate it manually, by hand, what are the steps you would take?

It gives the correct answer when you put 1.25 in cell B24, because the B24 calculation is outside the addition block. Move the Production function outside the addition block and it should then calculate correctly also:

Please Login or Register  to view this content.

This

1. Calculating 25% - 3000*25/100 = 750
2. 25% + existing value - 3000+750 = 3750

is not the same as this

=SUM(($AO29+$AV29)*PRODUCT($B$14:$B$21,1)+$AP29)*IF($B$24="",1,$B$24) taken from B4 in sample file in post 5

when B14 is 25%. I can't be sure what the correct calculation should be, as this is a field I, too, am not familiar with.

You might use the formula evaluation tool (http://office.microsoft.com/en-us/ex...932.aspx?CTT=1 click on "evaluate a nested formula one step at a time") to see the steps Excel is taking to obtain the result.

Sometimes I find it helpful to put a formula like this on paper in a more algebraic notation. With AO29=0, AV29=2500, B14=1.25, AP29=500 I get ((0+2500)*1.25+500)*1 -> 2500*1.25+500 -> 3125 + 500 -> 3625 Which is the correct result for the formula in B4. This is not the same thing as 3000*1.25=3750.

If you want ((0+2500)*1+500)*1.25, simply put the 1.25 into B24 instead of B14.

Or perhaps your formula is incorrect, and you wanted ((0+2500+500)*1.25)*1.

At this point, it seems to me that we aren't sure exactly what the calculation should look like. If you are correct, then the formula was incorrectly put into Excel. You should go back to the original source documentation for the formula to see what it should be, then re-enter the formula into Excel. If the formula in Excel is correct, then you are misunderstanding what it should be doing and should review what the exact form and intent of this calculation is. Either way, I would suggest going to the original documentation and determine how this calculation should be performed.