Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi,

    Can anyone tell me if there is a way around how HLOOKUP deals with zero's?
    I've attached a spreadsheet where HLOOKUP is used to let you know which was the last month the data was updated.
    For most month's it works fine however when the data returned is zero, it does not work.
    In column C the zero's (normally linked data) were overkeyed with 0.1 and formatted to no decimal places. The formula returned the correct month.
    In column E they were not and the formula returned the value in Cell A3.
    In the actual spreadsheet all of the cells B4 to F27 are linked to other sheets in the workbook.

    Is there a way to get Excel to return the correct month if a zero has been entered in the linked data as opposed to a blank cell?
    If the cell is blank, then data hasn't been entered yet.


    Thanks for any suggestions.

    capri
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If the zero cells that should be ignored are linked to other cells, then you should have the linking formula return "" rather than 0 when the source cell is blank.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks Rory,

    However that won't work in this spreadsheet. The person who constructed it used
    =SUMIF(OTHER!A:A,dateLookup!$A6,OTHER!C:C)
    to get the linked data.

    The formula has been copied down for the year. In the future months it returns a zero even though the cells are blank on the "OTHER" tab.


    capri

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    So how do you determine whether a zero is actually a valid answer, or it should be ignored?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    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
    Does changing the formula to:
    =IF(COUNTIF(OTHER!A:A,dateLookup!$A6)=0, "",SUMIF(OTHER!A:A,dateLookup!$A6,OTHER!C:C))

    Convert the zeroes to ignore to a null and keep the valid zeroes, allowing the lookup to work properly?

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Steve

    Thanks for the formula, however it did not do produce results any different than the original formula. All future months still had a zero result.


    Rory,

    I recently started a new job and this is from a spreadsheet developed by someone else. I am trying to figure out exactly what they have done. The spreadsheet feeds a dashboard. There are about 45 measures. Only 2 of the measures will ever have zero as they measure out of ordinary events that don't often occur. The target is zero, but occassionaly there is a figure form 1 to 3.

    I noticed in one column they had overkeyed the formula with 0.1 formatted to no decimal places. They did this for all past months with zero totals. This is not a good solution. In the other instance they left it as is and just put a comment on the dashboard spreadsheet that the cell was not working and then manually overkeyed it. This isn't a good solution either.

    I was able to figure out that the zero's cause the problem, but not having used the SUMIF formula for lookups before I don't know how to get around the problem. The formula is pasted in for the entire financial year and works fine in all the other columns. Even though the feeder sheets have blank cells, the SUMIF seems to produce a zero for all future months. SUMIF is needed as the data in the feeder spreadsheets is by region and we only report the overall total on the dashboard. I tried overkeying the zero's on the feeder sheets as 0.00001 and everything worked fine. It's not an ideal solution. I was hoping someone had run into a similar problem and found a formula that could deal with this type of situation.

    capri

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Steve's formula will work if the future dates have not already been entered in the source sheet. If they have, and the feeder cells are blank, rather than having zero in, then you could use SUMPRODUCT:

    =IF(SUMPRODUCT((OTHER!A2:A10000=dateLookup!$A6)*(O THER!C2:C10000<>""))=0,"",SUMIF(OTHER!A2:A10000,dateLookup!$A6,OTHER!C2:C100 00))


    or you could use a column that should not return zero normally and use that to determine the last actual date - based on your example, if ABC should return data, you could use:
    =LOOKUP(2,1/($B$4:$B$27<>0),$A$4:$A$27)


    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks Rory,

    I tried the SUMPRODUCT formula and it returns blanks in the cells for future months which is one step in the right direction.

    However the HLOOKUP still does not like the zero's so returns "Month" because the July entry is zero. If I overkey that with 0.00001 then it will return November because December is zero.


    I tried the Lookup formula and it works perfectly. Problem solved. I appreciate all your help.


    capri

Posting Permissions

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