Formula calculation is incorrect.

[supernova5271]

Hello everyone,

Working with Excel 2007 and Vista machine.
I have two sheets:
Sheet1: Sheet where I take lenght, width and height measurements.
Sheet2. Measurements from Sheet 1 are imported into sheet2 and once the respective columns are populated the volume is calculated.

The problem is for some reason the formula I have for calculating Volume just doesnt work.

For example the measurements are:
Lenght = 6.9 (Column D); Widht = 8.49 (Column E); Height = Blank and hidden (Column F);
Volume is calculated as:
Volume =IF(ISBLANK($D3),"",IF(ISBLANK($F3),IF($D3>$E3,$D3*($E3^2)*0.5,$E3*($D3^2)*0.5),$D3*$E3*$F3))

For some reason it always follows that value in column D is greater than value in column E and makes the calculation.

In this case the Volume should be 202 but it calculates the value to be 248.

I dont know whats wrong.
Can anyone help?

Thanks,

[6StringJazzer]

Based on your description, you are getting the wrong answer, so the problem must be in your workbook. Can you post your file?

BTW what kind of shapes are you calcluating volume for? I can't figure out how your formula works, or how you can leave one dimension blank.

[NBVC]

I get 202.104.

ARe you sure you have autocalculations on?

In the Formuals tab, go to Calculation Options and ensure you have Automatic on.

Or one/both your values are formatted as text.

Type a 0 in any free cell and copy it, then select D3 and E3 and then right-click Paste Special and select Add.

[supernova5271]

I have auto calculations on.

Both my values are numbers and not Text.

I am not sure whats happening.

Any help is appreciated.

[shg]

Post a workbook.

[supernova5271]

Attaching two sheets.

Open sheet2 and use the GetData Macro to Import the Sheet1.

You will see the Volume measurement is in correctly calculated in Sheet2 and differs from the one in sheet1 even though the calculations are same.

Thanks for the help,

[6StringJazzer]

I got a "subscript out of range" error running the macro, but could not diagnose it because the VBA is password-protected. The data loaded anyway. I can't rule out that the macro is part of the problem.

When I insert the formula

=D2>E2

I get a result of TRUE even though the result should be FALSE. This is causing your problem although the root cause it not evident (to me). I also tried

=E2-D2

and got the correct result of 1.59.

If I type by hand the same two values at some random place in the same workbook, I get the same odd result, but this doesn't happen if I do that in a new workbook. I also get the incorrect result if I copy the same data to a new workbook.

So I've done some poking around but I'm sorry that I still can't tell what you have to fix.

[6StringJazzer]

The value 6.9 is being pasted in as text. That's your problem.

ETA: I haven't seen anything quite like it before; there is no single-quote mark in the formula box to indicate that it's text, but when you modify the format to show additional decimal places, there is no change. And if I type "6.9" into the same cell everything works fine. So I'm pretty sure that is what is going on.

[supernova5271]

Thank you very much.

Thats very very useful.

Is there a way to force the cells to have only numbers and not text when I am importing the values from another sheet.

[6StringJazzer]

Is there a way to force the cells to have only numbers and not text when I am importing the values from another sheet.

Best direction at this point is for you to provide your VBA that is associated with the button.