formula not calculating correctly

asked 2019-04-11 20:59:27 +0100

Aurick gravatar image

updated 2019-04-12 15:52:49 +0100

erAck gravatar image

Not multiplying correctly. Cells:

  • B6 $750
  • E6 $0.10 (which is populated from sheet 1)
  • G6 $71.25, Which should equal B6*E6 but 750*0.10=75


edit retag flag offensive close merge delete


But there is something wrong with what you are trying to do. It seems you are multiplying dollars times dollars, which has no meaning, and will probably give an indeterminate result. Consider this : 6 apples times 2 is meaningful, but 6 apples times 2 apples is meaningless. Perhaps column E is actually a ratio of two numbers (perhaps a rate of interest?) in which case you should multiply B6 by 0.10 (not $0.10). Possible?

ve3oat gravatar imageve3oat ( 2019-04-11 22:00:14 +0100 )edit

I have tried this in Calc, and it works fine even multiplying a currency by a currency which may not make sense, but it works. I'd probably try a little more debugging, as if you try it in a blank document, it'll probably work. Attaching the document may help us, but you may need to strip out sensitive data.

Martin691 gravatar imageMartin691 ( 2019-04-11 22:27:54 +0100 )edit

Hint: when typing formulas here enclose them in backticks (you also get with Ctrl+K) otherwise the * asterisk multiplication operator here is a formatting operator for italics and gave B6E6 but 7500.10=75 in your example. I corrected that in your question (and also gave the cells as bullet list).

erAck gravatar imageerAck ( 2019-04-12 15:56:10 +0100 )edit

What is the exact formula in B6 and G6 cells and what is the result in G6 when you put 1000 instead of 750 in B6 cell?

SM_Riga gravatar imageSM_Riga ( 2019-04-12 17:41:56 +0100 )edit

you're right VE3OAT "0.10" is a weight not a dollar amount. I'm sorry, that's my bad. i'm sure that has to do with the difference.

Aurick gravatar imageAurick ( 2019-04-12 18:08:53 +0100 )edit

Hello @Aurick That's OK. If you make sure that numbers in column E are entered as pure numbers (unitless or dimensionless) and that they are all formatted as pure numbers, the formula should work alright. Formatting can sometimes have strange effects on the way numbers are treated in formulas.

ve3oat gravatar imageve3oat ( 2019-04-12 20:44:22 +0100 )edit