LinkBack URL
About LinkBacks
I've got a lookup function that sometimes will not find any matching values and thus display a zero as the result. Is there a way to format a cell to simply show a blank cell when this happens? Just don't want to see a zero.
Thanks
I've got a lookup function that sometimes will not find any matching values and thus display a zero as the result. Is there a way to format a cell to simply show a blank cell when this happens? Just don't want to see a zero.
Thanks
You either quit or become really good at it. There are no other choices.
A lookup that failed to find a match would normally return #N/A. So, what have you done to avoid that? If there is a match but the value returned is zero, that probably means the cell to be returned is empty/blank.
If the cell to be returned has alphanumeric content, you could just addthat's ampersand double quotes double quotes. Otherwise, you will need to test the value returned for blank.Formula:
Please Login or Register to view this content.
Doh! My apologies. I am using the index function instead of lookup, which basically returns the value of the row so =index(A1:A10,3) will return 0 if A3 is blank. I am aware of using "", but would like to avoid increasing the length of the formula as I have many of these.
You can't change the functionality of the formula without adding bits to it.
You could use conditional formatting to hide zero values by setting the font colour the same as the fill colour, but that would hide true zeros as well as those returned by empty cells.
edit:-
There is also an option 'Show a zero in cells that have a zero value' which can be applied to the whole worksheet (sheet, not book, so applying it to Sheet1 would still allow zeros to be shown on sheet2).
File - Excel Options - Advanced - Display options for this worksheet (select relevant sheet from dropdown).
second edit:-
InvisibleMan has provided a link with more info on this while I was typing my first edit.
Last edited by jason.b75; 03-24-2016 at 08:54 PM.
https://support.office.com/en-gb/art...hide_zero_valu
It will, of course, hide genuine zero values.
Well saidYou can't change the functionality of the formula without adding bits to it.
That works. Thanks!Originally Posted by InvisibleMan
You're welcome. Thanks for the rep.
Personally, I would go with the longer formula, for example
=IF(LEN(INDEX(A1:A10,3)&""),INDEX(A1:A10,3),"")
Which should cater for all data types (except formula errors).
If the value being returned is TEXT...
=T(INDEX(A1:A10,3))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
In terms of length of formula, there's not a lot in it
Formula:Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
If the values being returned where numbers you wouldn't want to use that technique. That will convert them into text values.
If the values being returned were numbers, you wouldn't want to use T() either. That will leave you with a sheet full of blanks.
Right, that's why I qualified my response with:
If the value being returned is TEXT...
I guess we're both guilty of not scrolling back far enough to check all of the relevant information in previous posts.
On the basis that excel handles alphanumeric entries as text strings, not numeric values, I read this as qualifying the use of &"" in the same way, otherwise I would have pointed out the potential issue it could cause in post #4.If the cell to be returned has alphanumeric content, you could just addthat's ampersand double quotes double quotes. Otherwise, you will need to test the value returned for blank.Formula:Please Login or Register to view this content.
Last edited by jason.b75; 03-25-2016 at 11:10 AM.
I was simply comparing the length of the formulae, not the impact of using them.If the values being returned where numbers you wouldn't want to use that technique. That will convert them into text values.
I originally said:And I supplemented that by sayingIf the cell to be returned has alphanumeric content, you could just add
Formula:Please Login or Register to view this content.Otherwise, you will need to test the value returned for blank.
OK, looks like we covered all the bases on this one.
Next...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks