Results 1 to 8 of 8
Thread: HLOOKUP and zero's

20101028, 22:53 #1
 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

20101029, 04:25 #2
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,324
 Thanks
 3
 Thanked 215 Times in 198 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

20101101, 16:44 #3
 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

20101101, 17:10 #4
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,324
 Thanks
 3
 Thanked 215 Times in 198 Posts
So how do you determine whether a zero is actually a valid answer, or it should be ignored?
Regards,
Rory
Microsoft MVP  Excel

20101101, 17:44 #5
 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

20101103, 19:21 #6
 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

20101104, 03:09 #7
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,324
 Thanks
 3
 Thanked 215 Times in 198 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

20101104, 20:11 #8
 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