Results 1 to 4 of 4

Thread: Treat Missing Values as 0?

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Treat Missing Values as 0?

    I've been working for sometime on creating an accurate payroll environment within excel, and I'm finally close, but ran into an issue I could use the expertise of this forum with. The workbook contains sheets for each month (January to December) separated by Quarterly sheets that populate from the appropriate monthly sheets, and a YTD sheet that sums the info from the Quarterly sheets. What I'm finding is that on the YTD sheet I'm getting missing value errors for the months that haven't come (or been entered yet). Is there a way to have excel treat these 0 value cells as a 0 and add up what is currently there? Here is an example of the code -

    =IFERROR(VLOOKUP(A22, January, 131, 0)+VLOOKUP(A22, February, 131, 0)+VLOOKUP(A22, March, 131, 0), 0)

    For example, lets say that I have entered January payroll, February and March are blank (well not blank as many cells contain a formula waiting on hours worked to be entered), and the Quarter1 sheet will give a Missing Value error, or rather just display 0, instead of showing the value for January.

    Thanks in advance

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,261
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Treat Missing Values as 0?

    Try:

    =IFERROR(VLOOKUP(A22, January, 131, 0), 0) + IFERROR(VLOOKUP(A22, February, 131, 0),0) + IFERROR(VLOOKUP(A22, March, 131, 0), 0)
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Apr 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Treat Missing Values as 0?

    Thank you! That did the trick.

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,261
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Treat Missing Values as 0?

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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