Vlookup Blanks as blanks not 0

[Rlopez24]

I am doing a Vlookup and it is returning 0 if there is a blank for that date but i want it to return the blank so when i take the average the blanks are not lowering the average with the zeros


Any help would great


Thank you.

 

[Debraj]

Hi Rlopez24,


So where do you want to change in Vlookup Part or in Average Part..

If Vlookup, Then

=IF(ISBLANK(VLOOKUP(Value,SearchTable,Column(),0)),"",VLOOKUP(Value,SearchTable,Column(),0))


else


[code]=AVERAGEIF(RangeArea,">"&0)

For excel below 2007,

=SUM(RangeArea)/COUNTIF(RangeArea,">"&0)[/code]


Regards,

Deb

 

[SirJB7]

Hi, Rlopez24!


If Debraj Roy weren't so perceptive and discerning it'd have been difficult for anyone who might read this to hit the mark with the provided info. Posting the link to sample file, copying & pasting values and formulas, specifying a couple of examples, that will surely help people to aid you better.


Regards!

 

[Rlopez24]

Thank you Deb that is exactly what i needed you are a life saver!

 

[shrivallabha]

You can also do the following:


Cells | Formatting | Custom and then add following custom format:

0;-0;""


whenever the result will be zero the cell will appear blank.

 

[SirJB7]

Hi, Rlopez24!

Take care of the differences between Debraj Roy's and shrivallabha's solutions: 1st one actually leaves a blank ("") as cell value while 2nd still leaves a zero (0) but displayed format edited.

Even if both are visually the same, as per your requirements shrivallabha's solution won't give you the right average.

Regards!