View Full Version : Help with Excel please

Hi, I'm sure there is an easy answer to this but haven't yet figured it out. I am writing a spreadsheet for expenses & am using the Sum function eg '=Sum(c5/9') to work out GST. My queston is, is there a way to hide the 0.00 values in a cell if the value is zero. At present I have a whole column of 0.00 when perhaps only a couple of whole figures (expenses) are being used to calculate from, and which vary for each account. Thanks if you can help.

I've since found that zero values can be hidden in cells by using formatting code '0;-0;;@'. This does a great job of hiding the zeros until required, but does however round up to a whole number. I would still like the GST to rounded to 2-decimal places. Your suggestions would be much appreciated.

There's an option to turn off zero values in spreadsheets. Have a look for it.

As to rounding down you may need to set the cell properties to do that. I haven't used Excel in a while.

You did not say which version of Excel you are using. In Win 9.x versions number format is set by a Number Format procedure which is what I presume you are using. In case not, in Excel 5 it is under Format, Cells, Number - it is elsewhere in v.4 and possibly others. There are 3 settings, separated by colons.The 1st defines positive numbers, the 2nd negatives and the third how zero is displayed. To get rounding to 2 dec places you need at least 0.00 in the 1st place (not 0, which rounds to whole numbers). You may also need a thousands indicator etc. To get a blank for a cell just leave it empty ie don't enter zero. If you want to leave blank a cell which has a CALCULATED value of 0 set your format as '(whatever);(whatever);' - ie don't make an entry in the 3rd position.

You may already know this but when when doing monetary and sometimes other spreadsheets it is often necessary to

set 'Precision as Displayed' to enabled (Tools, Options, Calculations tab). This forces Excel to use the rounded values for subsequent calculations, otherwise it uses the (not visible) unrounded values and this can produce discrepancies between eg the sum of a series of displayed values and the sum of the cells containing them as calculated by Excel.

Happy GST!

Thankyou Robin !!!

You solved the problem. Great explaination & much appreciated :-)

Steve,

If I understand your post,

you want to divide column C5

by 9,but if there is zero in

column C5 you do not want

0.00 to appear in the result

column.

This approach may help

In the result column use this formula

=IF(C5>0,C5/9,'')

Powered by vBulletin® Version 4.2.5 Copyright © 2019 vBulletin Solutions Inc. All rights reserved.