Results 1 to 5 of 5
  1. #1
    New Lounger
    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,
    Dale
    Attached Files Attached Files
    Last edited by Dale2013; 2013-08-01 at 18:59.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 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?

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by Dale2013 View Post
    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
    See answers in quote above.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    New Lounger
    Join Date
    Aug 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Perfect thanks a ton

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 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)

Posting Permissions

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