1. ## formula help and adding error

I am in need of help with a formula start with. My boss wants my sheet not to show all the zero's on the sheet. I do not know how to write a formula that hides the Zero's on the sheet if they are not in use.

My second issue is that when I put 22.5 in the FLSA line it automatically rounds up and we need the exact number to be in the cell and total up with out rounding up. I have attached the sheet any help would be much appreciated.

Third issue: My boss wants me to write a formula for meal allowances where I can put an X in the cell and the formula will add up and total the X's. Is there a simple formula for this that will not show a zero if unused?

I am using Excell 2010

Thanks,
Dale

2. First, Q28 is a circular reference and should not be: =SUM(J28:Q28) but probably be: =SUM(J28:P28)

Hiding Zeros: one way would be to go to: File, Options, Advanced and scroll about half way down and uncheck "show a zero in cells that have a zero value"

FLSA line: From the Home tab, in the Number Group, either increase the number of decimal places in the cells where you want to show 1 or more places, or click on the dialog box option and change the format to have 1 place (or whatever).

Counting X's: I thought the earlier attachment counted X's and multiplied by the allowance, but that seems to have been removed. In I23, for breakfast @ \$11, you could write: =countif(B23:H23,"x") and you'll get a count of the X's.
You could write: =11*countif(B23:H23,"x") and have the count of X's multiplied by \$11.

If you've set the option to not display zeros, then if there are no x's, the zero won't display.

Is this what you want for the meals?

3. Originally Posted by Dale2013
I am in need of help with a formula start with. My boss wants my sheet not to show all the zero's on the sheet. I do not know how to write a formula that hides the Zero's on the sheet if they are not in use.

Select: File -> Options -> Advanced.
Excel Hide Zero Values.JPG

My second issue is that when I put 22.5 in the FLSA line it automatically rounds up and we need the exact number to be in the cell and total up with out rounding up. I have attached the sheet any help would be much appreciated.
FormatCells.JPG
Note if you select all the cells in the area you can accomplish this in one right-click Format operation.

Third issue: My boss wants me to write a formula for meal allowances where I can put an X in the cell and the formula will add up and total the X's. Is there a simple formula for this that will not show a zero if unused?

This can be accomplished with a Sumif or SumIfs function but we need to know what cell you want the X's in and what cells should be summed, and lastly where the total goes.

HTH
I am using Excell 2010

Thanks,
Dale

4. Perfect thanks a ton

5. In this forum, it pays to be precise, to avoid confusion.
Is that a US ton or a UK ton?

zeddy

(FYI: US ton = 2,000 pounds; UK ton = 2,240 pounds)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•