Apr 27 2021 05:26 PM
Hi all ! I am having a bit of a bizarre experience with Multiplyinf cells in Excel. Very simple formula checked against a calculate and the answer is significantly incorrect... I have attached a copy of it and highlighted in red the offending cells. Any thoughts ?
As always thanking all in the community in advance :)
Apr 27 2021 09:17 PM
@Cambosity100 Well, you don't specify what exactly is wrong. Could it be that the formula in G8 is simply inconsistent with the one above it? Perhaps using =B8*C8*H8*F4 will solve the problem.
Apr 27 2021 09:41 PM
Apr 28 2021 06:30 AM - edited Apr 28 2021 05:58 PM
SolutionRe: ``Very simple formula checked against a calculate and the answer is significantly incorrect``
You neglect to provide even a single example of the Excel result vs the calculator result.
But we might guess that you are making a very common mistake: when you enter values into the calculator, you enter them as they are displayed in Excel.
But the displayed values might be different from the actual values; and Excel uses the actual values in calculations.
For example, the formula in G7 is =B7*C7*H7*F3. B7 is 1, C7 is 77.81, H7 is 4, and F3 appears to be 39. And the result in G7 appears to be $12,093.90.
But if we enter 1*77.81*4*39 into a calculator, the result is $12,138.36. That is indeed very different.
The problem is: F3 only appears to be 39 because of the cell format. Its actual value is 38.8571428571429. And that is the value that Excel uses in the calculation.
To demonstrate, if we enter 1*77.81*4*38.85714 into the calculator (and display only 2 decimal places), the result appears to be $12,093.90 -- the same as G7.
And BTW, G7 only appears to be $12,093.90 because of the cell format. Its actual value is 12093.8971428571.
Only you can decide if and where to explicitly round Excel calculations. For example, you might write:
F3: =ROUND((E3-D3)/7, 0)
G7: =ROUND(B7*C7*H7*F3, 2)
(Arguably, rounding G7 is unnecessary if we round F3, at least for the example Excel file. But it is prudent to explicitly round calculations that involve decimal fractions for other reasons.)
Alternatively, you might choose to leave F3 unrounded for other purposes, but round its value when calculating G7. So, you might write:
F3: =(E3-D3)/7
G7: =ROUND(B7*C7*H7*ROUND(F3, 0), 2)
But in the final analysis, you might choose not to round at all, accepting the fact that the Excel calculation is more "accurate" (for some purposes) and different from the WYSIWYG calculations that you might enter on a calculator.
Aside.... There is no need to use DATEDIF(...,"d"); E3-D3 is simpler. And there is no need for the outer parentheses around the entire formula expression after the equal sign ("=").
-----
Caveat: Someone might suggest setting the option "Precision as displayed" to avoid the explicit rounding. I do not recommend that, for many reasons. But if you choose to experiment with PAD, be sure to make a copy of the Excel file first. Merely setting PAD might irreversibly change constants that you purposely display with less precision. Setting PAD affects all worksheets change in the entire Excel file. And PAD affects only the final value in a cell that is formatted as intended; for example, =IF(F3=39, TRUE) would still return FALSE(!).
Apr 29 2021 02:00 AM
Apr 29 2021 02:03 AM
Apr 29 2021 02:07 AM
Apr 29 2021 02:08 AM
Apr 28 2021 06:30 AM - edited Apr 28 2021 05:58 PM
SolutionRe: ``Very simple formula checked against a calculate and the answer is significantly incorrect``
You neglect to provide even a single example of the Excel result vs the calculator result.
But we might guess that you are making a very common mistake: when you enter values into the calculator, you enter them as they are displayed in Excel.
But the displayed values might be different from the actual values; and Excel uses the actual values in calculations.
For example, the formula in G7 is =B7*C7*H7*F3. B7 is 1, C7 is 77.81, H7 is 4, and F3 appears to be 39. And the result in G7 appears to be $12,093.90.
But if we enter 1*77.81*4*39 into a calculator, the result is $12,138.36. That is indeed very different.
The problem is: F3 only appears to be 39 because of the cell format. Its actual value is 38.8571428571429. And that is the value that Excel uses in the calculation.
To demonstrate, if we enter 1*77.81*4*38.85714 into the calculator (and display only 2 decimal places), the result appears to be $12,093.90 -- the same as G7.
And BTW, G7 only appears to be $12,093.90 because of the cell format. Its actual value is 12093.8971428571.
Only you can decide if and where to explicitly round Excel calculations. For example, you might write:
F3: =ROUND((E3-D3)/7, 0)
G7: =ROUND(B7*C7*H7*F3, 2)
(Arguably, rounding G7 is unnecessary if we round F3, at least for the example Excel file. But it is prudent to explicitly round calculations that involve decimal fractions for other reasons.)
Alternatively, you might choose to leave F3 unrounded for other purposes, but round its value when calculating G7. So, you might write:
F3: =(E3-D3)/7
G7: =ROUND(B7*C7*H7*ROUND(F3, 0), 2)
But in the final analysis, you might choose not to round at all, accepting the fact that the Excel calculation is more "accurate" (for some purposes) and different from the WYSIWYG calculations that you might enter on a calculator.
Aside.... There is no need to use DATEDIF(...,"d"); E3-D3 is simpler. And there is no need for the outer parentheses around the entire formula expression after the equal sign ("=").
-----
Caveat: Someone might suggest setting the option "Precision as displayed" to avoid the explicit rounding. I do not recommend that, for many reasons. But if you choose to experiment with PAD, be sure to make a copy of the Excel file first. Merely setting PAD might irreversibly change constants that you purposely display with less precision. Setting PAD affects all worksheets change in the entire Excel file. And PAD affects only the final value in a cell that is formatted as intended; for example, =IF(F3=39, TRUE) would still return FALSE(!).