SOLVED

Simple cell multiplication returning incorrect answers

%3CLINGO-SUB%20id%3D%22lingo-sub-2300126%22%20slang%3D%22en-US%22%3ESimple%20cell%20multiplication%20returning%20incorrect%20answers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2300126%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%20!%20I%20am%20having%20a%20bit%20of%20a%20bizarre%20experience%20with%20Multiplyinf%20cells%20in%20Excel.%20Very%20simple%20formula%20checked%20against%20a%20calculate%20and%20the%20answer%20is%20significantly%20incorrect...%20I%20have%20attached%20a%20copy%20of%20it%20and%20highlighted%20in%20red%20the%20offending%20cells.%20Any%20thoughts%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20always%20thanking%20all%20in%20the%20community%20in%20advance%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2300126%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2300505%22%20slang%3D%22en-US%22%3ERe%3A%20Simple%20cell%20multiplication%20returning%20incorrect%20answers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2300505%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421224%22%20target%3D%22_blank%22%3E%40Cambosity100%3C%2FA%3E%26nbsp%3BWell%2C%20you%20don't%20specify%20what%20exactly%20is%20wrong.%20Could%20it%20be%20that%20the%20formula%20in%20G8%20is%20simply%20inconsistent%20with%20the%20one%20above%20it%3F%20Perhaps%20using%26nbsp%3B%3CSTRONG%3E%3DB8*C8*H8*F4%20%3C%2FSTRONG%3Ewill%20solve%20the%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2300995%22%20slang%3D%22en-US%22%3ERe%3A%20Simple%20cell%20multiplication%20returning%20incorrect%20answers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2300995%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421224%22%20target%3D%22_blank%22%3E%40Cambosity100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERe%3A%20%60%60Very%20simple%20formula%20checked%20against%20a%20calculate%20and%20the%20answer%20is%20significantly%20incorrect%60%60%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20neglect%20to%20provide%20even%20a%20single%20example%20of%20the%20Excel%20result%20vs%20the%20calculator%20result.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20we%20might%20guess%20that%20you%20are%20making%20a%20very%20common%20mistake%3A%26nbsp%3B%20when%20you%20enter%20values%20into%20the%20calculator%2C%20you%20enter%20them%20as%20they%20are%20displayed%20in%20Excel.%26nbsp%3B%20But%20the%20displayed%20values%20might%20be%20different%20from%20the%20actual%20values%3B%20and%20Excel%20uses%20the%20actual%20values%20in%20calculations.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20the%20formula%20in%20G7%20is%20%3DB7*C7*H7*F3.%20B7%20is%201%2C%20C7%20is%2077.81%2C%20H7%20is%204%2C%20and%20F3%20appears%20to%20be%2039.%20And%20the%20result%20in%20G7%20appears%20to%20be%20%2412%2C093.90.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20if%20we%20enter%201*77.81*4*39%20into%20a%20calculator%2C%20the%20result%20is%20%2412%2C138.36.%26nbsp%3B%20That%20is%20indeed%20very%20different.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%3A%26nbsp%3B%20F3%20only%20appears%20to%20be%2039%20because%20of%20the%20cell%20format.%26nbsp%3B%20It%20is%20actually%2038.8571428571429.%26nbsp%3B%20And%20that%20is%20the%20value%20that%20Excel%20uses%20in%20the%20calculation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20demonstrate%2C%20if%20we%201*77.81*4*38.85714%20into%20the%20calculator%20(and%20display%20only%202%20decimal%20places)%2C%20the%20result%20appears%20to%20be%20%2412%2C093.90%20--%20the%20same%20as%20G7.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnly%20you%20can%20decide%20if%20and%20where%20to%20explicit%20round%20Excel%20calculations%20in%20order%20to%20match%20calculator%20calculations.%26nbsp%3B%20For%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EF3%3A%20%3D%3CFONT%20color%3D%22%23FF0000%22%3EROUND(%3C%2FFONT%3EDATEDIF(D3%2CE3%2C%22d%22)%2F7%3CFONT%20color%3D%22%23FF0000%22%3E%2C%200)%3C%2FFONT%3E%3C%2FP%3E%3CP%3EG7%3A%20%3D%3CFONT%20color%3D%22%23FF0000%22%3EROUND(%3C%2FFONT%3EB7*C7*H7*F3%3CFONT%20color%3D%22%23FF0000%22%3E%2C%202)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3E-----%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3ECaveat%3A%26nbsp%3B%20Someone%20might%20suggest%20setting%20the%20option%20%22Precision%20as%20displayed%22%20to%20avoid%20the%20explicit%20rounding.%26nbsp%3B%20%3CFONT%20color%3D%22%23FF0000%22%3EI%20do%20not%20recommend%20that%3C%2FFONT%3E%2C%20for%20many%20reasons.%26nbsp%3B%20But%20if%20you%20choose%20to%20experiment%20with%20PAD%2C%20be%20sure%20to%20%3CFONT%20color%3D%22%23FF0000%22%3Emake%20a%20copy%20of%20the%20Excel%20file%20first%3C%2FFONT%3E.%26nbsp%3B%20Merely%20setting%20PAD%20might%20%3CFONT%20color%3D%22%23FF0000%22%3Eirreversible%20change%20constants%3C%2FFONT%3E%20that%20you%20purposely%20display%20with%20less%20precision.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2302179%22%20slang%3D%22en-US%22%3ERe%3A%20Simple%20cell%20multiplication%20returning%20incorrect%20answers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2302179%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421224%22%20target%3D%22_blank%22%3E%40Cambosity100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERe%3A%20%60%60Very%20simple%20formula%20checked%20against%20a%20calculate%20and%20the%20answer%20is%20significantly%20incorrect%60%60%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20neglect%20to%20provide%20even%20a%20single%20example%20of%20the%20Excel%20result%20vs%20the%20calculator%20result.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20we%20might%20guess%20that%20you%20are%20making%20a%20very%20common%20mistake%3A%26nbsp%3B%20when%20you%20enter%20values%20into%20the%20calculator%2C%20you%20enter%20them%20%3CU%3E%3CEM%3Eas%20they%20are%20displayed%3C%2FEM%3E%3C%2FU%3E%20in%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20the%20displayed%20values%20might%20be%20different%20from%20the%20actual%20values%3B%20and%20Excel%20%3CU%3E%3CEM%3Euses%20the%20actual%20values%3C%2FEM%3E%3C%2FU%3E%20in%20calculations.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20the%20formula%20in%20G7%20is%20%3DB7*C7*H7*F3.%20B7%20is%201%2C%20C7%20is%2077.81%2C%20H7%20is%204%2C%20and%20F3%20%3CU%3E%3CEM%3Eappears%20to%20be%3C%2FEM%3E%3C%2FU%3E%2039.%20And%20the%20result%20in%20G7%20%3CU%3E%3CEM%3Eappears%20to%20be%3C%2FEM%3E%3C%2FU%3E%20%2412%2C093.90.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20if%20we%20enter%201*77.81*4*39%20into%20a%20calculator%2C%20the%20result%20is%20%3CFONT%20color%3D%22%23FF0000%22%3E%2412%2C138.36%3C%2FFONT%3E.%26nbsp%3B%20That%20is%20indeed%20very%20different.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%3A%26nbsp%3B%20F3%20only%20%3CU%3E%3CEM%3Eappears%20to%20be%3C%2FEM%3E%3C%2FU%3E%2039%20because%20of%20the%20cell%20format.%26nbsp%3B%20Its%20%3CEM%3E%3CU%3Eactual%20value%3C%2FU%3E%3C%2FEM%3E%20is%20%3CFONT%20color%3D%22%23FF0000%22%3E38.8571428571429%3C%2FFONT%3E.%26nbsp%3B%20And%20that%20is%20the%20value%20that%20Excel%20uses%20in%20the%20calculation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20demonstrate%2C%20if%20we%20enter%201*77.81*4*%3CFONT%20color%3D%22%23FF0000%22%3E38.85714%3C%2FFONT%3E%20into%20the%20calculator%20(and%20display%20only%202%20decimal%20places)%2C%20the%20result%20appears%20to%20be%20%2412%2C093.90%20--%20the%20same%20as%20G7.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20BTW%2C%20G7%20only%20%3CU%3E%3CEM%3Eappears%20to%20be%3C%2FEM%3E%3C%2FU%3E%20%2412%2C093.90%20because%20of%20the%20cell%20format.%20Its%20%3CEM%3E%3CU%3Eactual%20value%3C%2FU%3E%3C%2FEM%3E%20is%2012093.8971428571.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnly%20you%20can%20decide%20if%20and%20where%20to%20explicitly%20round%20Excel%20calculations.%26nbsp%3B%20For%20example%2C%20you%20might%20write%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EF3%3A%20%3D%3CFONT%20color%3D%22%23FF0000%22%3EROUND(%3C%2FFONT%3E(E3-D3)%2F7%3CFONT%20color%3D%22%23FF0000%22%3E%2C%200)%3C%2FFONT%3E%3C%2FP%3E%3CP%3EG7%3A%20%3D%3CFONT%20color%3D%22%23FF0000%22%3EROUND(%3C%2FFONT%3EB7*C7*H7*F3%3CFONT%20color%3D%22%23FF0000%22%3E%2C%202)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlternatively%2C%20you%20might%20choose%20to%20leave%20F3%20unrounded%20for%20other%20purposes%2C%20but%20round%20its%20value%20when%20calculating%20G7.%26nbsp%3B%20So%2C%20you%20might%20write%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EF3%3A%20%3D(E3-D3)%2F7%3C%2FP%3E%3CP%3EG7%3A%20%3DB7*C7*H7*%3CFONT%20color%3D%22%23FF0000%22%3EROUND(%3C%2FFONT%3EF3%3CFONT%20color%3D%22%23FF0000%22%3E%2C%200)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20in%20the%20final%20analysis%2C%20you%20might%20choose%20not%20to%20round%20at%20all%2C%20accepting%20the%20fact%20that%20the%20Excel%20calculation%20is%20more%20%22accurate%22%20(for%20some%20purposes)%20and%20different%20from%20the%20WYSIWYG%20calculations%20that%20you%20might%20enter%20on%20a%20calculator.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAside....%20There%20is%20no%20need%20to%20use%20DATEDIF(...%2C%3CFONT%20color%3D%22%23FF0000%22%3E%22d%22%3C%2FFONT%3E)%3B%20E3-D3%20is%20simpler.%26nbsp%3B%20And%20there%20is%20no%20need%20for%20the%20outer%20parentheses%20around%20the%20entire%20formula%20expression%20after%20the%20equal%20sign%20(%22%3D%22).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3E-----%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3ECaveat%3A%26nbsp%3B%20Someone%20might%20suggest%20setting%20the%20option%20%22Precision%20as%20displayed%22%20to%20avoid%20the%20explicit%20rounding.%26nbsp%3B%20%3CFONT%20color%3D%22%23FF0000%22%3EI%20do%20not%20recommend%20that%3C%2FFONT%3E%2C%20for%20many%20reasons.%26nbsp%3B%20But%20if%20you%20choose%20to%20experiment%20with%20PAD%2C%20be%20sure%20to%20%3CFONT%20color%3D%22%23FF0000%22%3Emake%20a%20copy%20of%20the%20Excel%20file%20first%3C%2FFONT%3E.%26nbsp%3B%20Merely%20setting%20PAD%20might%20%3CFONT%20color%3D%22%23FF0000%22%3Eirreversibly%20change%20constants%3C%2FFONT%3E%20that%20you%20purposely%20display%20with%20less%20precision.%26nbsp%3B%20Setting%20PAD%20affects%20%3CFONT%20color%3D%22%23FF0000%22%3Eall%20worksheets%20change%3C%2FFONT%3E%20in%20the%20entire%20Excel%20file.%26nbsp%3B%20And%20PAD%20affects%3CFONT%20color%3D%22%23FF0000%22%3E%20only%20the%20final%20value%20in%20a%20cell%3C%2FFONT%3E%20that%20is%20formatted%20as%20intended%3B%20for%20example%2C%20%3DIF(F3%3D39%2C%20TRUE)%20would%20still%20return%20FALSE(!).%3CBR%20%2F%3E%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

7 Replies

@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.

 

 

You have few DATEDIF & others and all are producing correct result,, plzz be specific formula in which cell returns wrong results !!
best response confirmed by Cambosity100 (Occasional Contributor)
Solution

@Cambosity100 

Re: ``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(!).

Firstly my apologies for neglecting to give an accurate example of sheet vs calculator. A an obvious oversight. Secondly you have completely nailed it. I was not aware of the ROUND function and now with your help the sheet works perfectly. Many thanks. Also thankyou for the aside. I got the DATEDIF from the Excel help. I did a search for "calculate the difference between two dates" and it was what help came up with. Yours is MUCH simpler and elegant. Once again many thanks to all who replied...
Sorry for that oversight. Joe below has solved it for me. Thankyou for taking the time to look. It is appreciated...
Sorry for that oversight Riny. Joe below has solved it for me. Thankyou for taking the time to look. It is appreciated...
Sorry for that oversight Rajesh. Joe below has solved it for me. Thankyou for taking the time to look. It is appreciated...