Decimal summing problem

[Darren Lee Wells]

I am having a problem when summing up cells that contain decimals. This is because in that the view of the spreadsheet we are presenting to a client is set to show numbers but with no decimal places, but when they are summed up the decimals are still included in the equation and it looks like the total is wrong. The spreadsheet is showing figures such as average order value, estimated website traffic, orders, conversion etc.

The cells containing the estimated order numbers are made up by multplying traffic numbers (so 150,000 for example) by a conversion percentage (say 4.5%). This obviously leaves decimals for the order numbers which is not correct and we need to round these up but we dont want to do this instantly as we need to leave them as formulas so when we change other variables in the sum, the order numbers update in line with this accordingly.

Initially we tried doing this through simply changing the cell formatting to show a number with no decimal places which looks right in the table but then the sum of the orders includes decimals. We also tried using the round function on all the cells which does work but means the cells are then no longer formulas and if other variables on the sheet change we have to redo all the order numbers.

So essentially is there a simple way to sum the value of cells that are shown rather than there true cell value including decimals or any other suitable alternative? Any help would be much appreciated.

[DonkeyOte]

Welcome to the Board.

is there a simple way to sum the value of cells that are shown rather than there true cell value including decimals

Some might suggest Precision as Displayed but I would say this is a (very) bad idea.

We also tried using the round function on all the cells which does work but means the cells are then no longer formulas

I'm afraid here I don't follow... the ROUND can be used in conjunction with as opposed to in replacement of another formula, ie

=ROUND(A1*4.5%,0)

will round result of A1*4.5% to nearest whole number, if A1 alters so will the above.

(on an aside you earlier mentioned rounding up - if that's the case use ROUNDUP (or CEILING if you wish to round up by a specific value))