HLOOKUP and zero's

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

So how do you determine whether a zero is actually a valid answer, or it should be ignored?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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)*(OTHER!C2:C10000″”))=0,””,SUMIF(OTHER!A2:A10000,dateLookup!$A6,OTHER!C2:C10000))

[/size]
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:[/size]
=LOOKUP(2,1/($B$4:$B$270),$A$4:$A$27)[/size]

[/size]

Reply | Quote

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

Reply | Quote