[Solved] cell won't format to 3 decimal places when use this formula

[DaveM1]

Hello,

I am hoping someone can help me get the following to work.
Cell H8 is formated as a number with #,##0.000;[RED]-#,##0.000
Cells D8, E8, and F8 are formated as currency [$$-409]#,##0.00;[RED]-[$$-409]#,##0.00
Cell G8 is formatted as currency [$$-409]#,##0.000;[RED]-[$$-409]#,##0.000

So when I divide D,E, or F8 by G8 I want a pure number with three ceimal places. Positive black and negative red.

However, the formula below gives a decimal with a ton of digits.
Cell H8 =IF(OR(OR(D8="",D8=0),G8=0),"",D8/G8)&IF(OR(OR(E8="",E8=0),G8=0),"",E8/G8)&IF(OR(OR(F8="",F8=0),G8=0),"",F8/G8)

What’s more interesting is that the statement below works to three decimal places.

=IF(OR(OR(D8="",D8=0),G8=0),"",D8/G8)

Or is this a bug which I should report?

Any suggestions are greatly appreciated.

V/R
Dave

[bencomp]

Please mark the answer that worked “correct” instead of editing the question. Thanks.

[horst]

As I see it the “&”-sign you used in the first formula adds up strings. So you get three numbers formatted in General Format as one big string.

See also Help: Operators.

[mariosv]

HI @DaveM1, I hope I have understand your question.

Cell format doesn’t change the cell content, or the result of their formula, so if you want limit the decimal places of a cell value, use a function like ROUND().

There is an option to use the values as shown, by dependent formulas.
Menu/Tools/Options/LibreOffice calc/Calculation - Precision as shown.
With the option off, calculations are done with all decimals again, because it doesn’t change the cell values only how they are taken by dependent formulas.

In my opinion the more secure way is the first, so you always have the control, and can avoid accumulate errors e.g summing a column of monetary values, the option can be changed without think/know their implications or simply by error.

[DaveM1]

Thanks all,

I originally wanted to test each field to see if it was filled in then perfom the operation. I couldn’t do that so I made this work around:

=IF(OR(OR(SUM(D8+E8+F8)="",SUM(D8+E8+F8)=0),G8=0),"",SUM(D8+E8+F8)/G8)

It seems to be doing the job.

Thanks again,
Dave

[Lupp]

@DaveM1 - You did again mix up the operators.

Commenting on SUM(D8+E8+F8)="" :

1. D8+E8+F8 did already add three values. Apllying SUM() to this ONE result will not do anything.

2. You seem to intend a test for “All three cells blank?” but you will get a number on the left side and the empty string on the right side will automatically be converted into a number for comparison. Thus the result will be identical to that of SUM(D8+E8+F8)=0 .
   D8&E8&F8="" would do what you intended.

[DaveM1]

Thanks, one can tell I don’t do this for a living

Here is what I am / was trying to do:
‘Purchase Cost’ ‘Dividend Reinvest’ ‘Fees’ ‘Price’ ‘Shares’
determine which field Purchase, Reinvest, or Fees had an entry and if Price had an entry then divide the cost field by price to get the number of shares.
I wanted to avoid division by zero or null values because these fields fed my other calculations.

I clearly see what you mean though, and I will remove the sum function from my statement. That should work.
Cheers,
Dave