Index/match - make blank cells return a blank value.
 

I have the following formula:

=INDEX(Summary!$E$2:$E$79,MATCH(VendorCommodities_ Detail!$C3,Summary!$C$2:$C$79,0))

It caluculates correctly. But, I am working with a column that tracks
percent changes. Right now if the column is blank (I have yet to put a
percent in it) the formula reutrns a 0%. But, many times there is no change
in percent, and the 0% is truely accurate. I need to be able to distinguish
the dirrerence between the actual 0% and the one that says it is 0% due to no
value being entered into the index cell.

My intitial thought is make it return a blank cell but I am not sure how,
and I am not sure if thoses blank cells will mess up other caluculations
(sums, avgs, etc) that calculate off those cells.

What is the best way to go about this?

Thanks,
Diane

Answer: Index/match - make blank cells return a blank value.
 

Hi Diane,

I understand that you want to make the formula return a blank cell instead of 0% when the index cell is blank. You can modify the formula by adding an IF statement to check if the index cell is blank, and return a blank cell if it is. Here's the modified formula:

1. =IF(ISBLANK(INDEX(Summary!$E$2:$E$79,MATCH(VendorC ommodities_Detail!$C3,Summary!$C$2:$C$79,0))), "", INDEX(Summary!$E$2:$E$79,MATCH(VendorCommodities_D etail!$C3,Summary!$C$2:$C$79,0)))

This formula uses the ISBLANK function to check if the index cell is blank. If it is, the formula returns a blank cell (""). If it is not blank, the formula returns the result of the original formula.

Regarding your concern about the blank cells affecting other calculations, it depends on how those calculations are set up. If the calculations use the same IF statement to check for blank cells, they should not be affected. However, if the calculations assume that the cells contain 0%, they may need to be modified to handle blank cells.

Index/match - make blank cells return a blank value.
 

AVERAGE and SUM ignore text (a "" is text) so you can use blanks

=IF(INDEX(B1:B4,MATCH(D1,A1:A4,0))="","",INDEX(B1: B4,MATCH(D1,A1:A4,0)))


adapt to fit your sheet names and ranges


--
Regards,

Peo Sjoblom



"diaare" wrote in message
...
I have the following formula:

=INDEX(Summary!$E$2:$E$79,MATCH(VendorCommodities_ Detail!$C3,Summary!$C$2:$C$79,0))

It caluculates correctly. But, I am working with a column that tracks
percent changes. Right now if the column is blank (I have yet to put a
percent in it) the formula reutrns a 0%. But, many times there is no
change
in percent, and the 0% is truely accurate. I need to be able to
distinguish
the dirrerence between the actual 0% and the one that says it is 0% due to
no
value being entered into the index cell.

My intitial thought is make it return a blank cell but I am not sure how,
and I am not sure if thoses blank cells will mess up other caluculations
(sums, avgs, etc) that calculate off those cells.

What is the best way to go about this?

Thanks,
Diane

Index/match - make blank cells return a blank value.
 

Perfect.

Thanks

"Peo Sjoblom" wrote:

AVERAGE and SUM ignore text (a "" is text) so you can use blanks

=IF(INDEX(B1:B4,MATCH(D1,A1:A4,0))="","",INDEX(B1: B4,MATCH(D1,A1:A4,0)))


adapt to fit your sheet names and ranges


--
Regards,

Peo Sjoblom



"diaare" wrote in message
...
I have the following formula:

=INDEX(Summary!$E$2:$E$79,MATCH(VendorCommodities_ Detail!$C3,Summary!$C$2:$C$79,0))

It caluculates correctly. But, I am working with a column that tracks
percent changes. Right now if the column is blank (I have yet to put a
percent in it) the formula reutrns a 0%. But, many times there is no
change
in percent, and the 0% is truely accurate. I need to be able to
distinguish
the dirrerence between the actual 0% and the one that says it is 0% due to
no
value being entered into the index cell.

My intitial thought is make it return a blank cell but I am not sure how,
and I am not sure if thoses blank cells will mess up other caluculations
(sums, avgs, etc) that calculate off those cells.

What is the best way to go about this?

Thanks,
Diane