Results 1 to 5 of 5
Thread: formula help and adding error

20130801, 17:46 #1
 Join Date
 Aug 2013
 Posts
 5
 Thanks
 0
 Thanked 0 Times in 0 Posts
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,
DaleLast edited by Dale2013; 20130801 at 17:59.

20130801, 19:03 #2
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,593
 Thanks
 44
 Thanked 77 Times in 72 Posts
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?

20130801, 19:12 #3
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 10,007
 Thanks
 423
 Thanked 1,608 Times in 1,452 Posts
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20130801, 20:48 #4
 Join Date
 Aug 2013
 Posts
 5
 Thanks
 0
 Thanked 0 Times in 0 Posts
Perfect thanks a ton

20130802, 02:53 #5
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,442
 Thanks
 166
 Thanked 651 Times in 619 Posts
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)