Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Worthington, Ohio, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calc Formuals - Elminating Zeros (Excel 2000)

    I need to calculate formulas for past months but do not want to add in future months that contain a "0". I need to put "0s" in this equation so that I don't get the DIV/0 error. I know that I can enter and "IF" statement but want to avoid doing that. Is there a way I can say, Ignore future months that contain "0"? I don't want to say "ignore all "0s"" because we do have "0s" on Sundays when we are not open. Does this make sense?

    Thanks!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Calc Formuals - Elminating Zeros (Excel 2000)

    If your dates are in A2:A100 and your numbers are in B2:B100 you can use the array formula (confirm with ctrl-shift-enter):

    =SUM(IF(A2:A100=<TODAY(),B2:B100))

    you can use any of the stat functions:
    =Average(IF(A2:A100=<TODAY(),B2:B100))

    Change the ranges as appropriate

    You could also (depending on how your data is setup, create a variable-lenght range name using OFFSET. The length of th range would be based on number of rows of data, which could be calculated various ways.

    Steve

Posting Permissions

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