Hi guys,
Please find attached the sample excel file I have been working on so far.
Can anyone help me to get rid of the #N/A from the cells that does not match the criteria?
I've tried ISNA already and it didn't seem working properly on my excel function.
Any help will be greatly appreciated.
Plus, any idea why column K generate '0' value?
1. To get rid of #N/A
2. And also to get rid of '0' from column K
Cheers,
In query #1 - you can check for cell K first
=if(K17="","",your existing formula here... )
your second query
i think there will be effect co'z your match is using 0 but you can do it like this
=iferror(if(your index/match formula)=0,"",your index/match formula),"")
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
Hi Vlady,
Please forgive my ignorance.
Would you be able to show me how to apply the 'If fuction' you have suggested above to my excel formula using the 'sample excel file' that I have attached above?
That way, I would be able to have a clearer understanding of your suggestion.
Cheers,
in cell O17
Formula:
Please Login or Register to view this content.
in cell K17 is a problem regarding displaying blank for zero because 0 is being utilize in the match, but this is a formula ( i think not applicable) just for the sake of controling the 0 to become blank
Formula:Please Login or Register to view this content.
the bold parts logically test the outcome of the index/match if it is zero then display blank/null ""
Hi Vlady. By using the formula you suggested, I could finally get rid of #N/A from my excel spreadsheet. However, Problem 2 still remain unfixed. Seems like it is not fixable usnig 'fuctions'. But I really appreciate your help fixing the problem 1. Thans M8. Merry Christmas.
yes, you need all values including the 0 for the formula... but if yu don't want to see it. you can uncheck the excel options in
Option ->Advanced -> scroll down search for the "Show a zero in cell tha have zero values" untick the box. It will hide zero values...
Regards
vladimir
try this.
K17
Formula:Please Login or Register to view this content.
L17
Formula:Please Login or Register to view this content.
GOSH.....smart89. Your formula worked. Unbelievable. Thanks m8.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks