How to make a zero not show when connected to a formula?

[mlbdc2012]

In my attached worksheet, I have a list of 10 names with projected stats for 2015. Each name falls under either a Hitting Projection or Pitching Projection based on what position they play.

For example, the first name on the list is a hitter so his pitching projections stats should be blank instead they are showing up as zeros. I have formulas in those cells in my master copy but couldn't leave the formulas here because it's pulling from another file on my hard drive.

For cell AE13, my formula is as follows...=IFERROR(INDEX('Player Database'!$FB$12:$FB$1625,MATCH(C13,'Player Database'!$C$12:$C$1625,0))," ") but yet for some reason it's returning an error as a zero instead of a blank. Anyone know why?

Thanks!

[AlKey]

Try this formula

***Untested

=IFERROR(1/(1/INDEX('Player Database'!$FB$12:$FB$1625,MATCH(C13,'Player Database'!$C$12:$C$1625,0))),"")

[Glenn Kennedy]

Your formula wIll return an error only if C3 contains a value that isn't in column C of your raw data (and your iferror statement will hide it). If it IS present, but theres a zero or blank value associated with it in column FB of your raw data, it will return a 0. To get rid of it, use this:

Please Login or Register  to view this content.

[vlady]

or also

=IFERROR(INDEX('Player Database'!$FB$12:$FB$1625,MATCH(C13,'Player Database'!$C$12:$C$1625,0)),"")&""

Edit: the above will return as text strings

[mlbdc2012]

Try this formula

***Untested

=IFERROR(1/(1/INDEX('Player Database'!$FB$12:$FB$1625,MATCH(C13,'Player Database'!$C$12:$C$1625,0))),"")

Yes that works, what did you do? Thanks!

[mlbdc2012]

Yes that works, what did you do? Thanks!

The only problem is that this now makes a stat 0 for a hitter if they aren't projected to get any stats in that category. I guess there is nothing I can do about that unless there was a way for the formula to recognize what position the player plays and then either show the zero or make the zero show as blank. Anyway to do this?

[AlKey]

When a formula returns a 0 the IFERROR function is not effective and therefore IFERROR(1/(1 where 1 is dived by 0 to create an error and now it will be remedied by IFERROR.


another way is to use Conditional Formatting to hide zeros.

[Glenn Kennedy]

It now might be easier if we had a sample of your sheet to play with....

[mlbdc2012]

It now might be easier if we had a sample of your sheet to play with....

I attached my sheet in my first post.

[Glenn Kennedy]

Apologies for the cryptic comments. You HAD uploaded a sheet, but there are no raw data on that sheet and no formulas, so it wasn't/isn't much help!!

Can you upload a small sample of representative data?

[mlbdc2012]

Apologies for the cryptic comments. You HAD uploaded a sheet, but there are no raw data on that sheet and no formulas, so it wasn't/isn't much help!!

Can you upload a small sample of representative data?

Ok here's my newly attached file, I would like Clayton Kershaw and Felix Hernandez G and SB cells to be blank but still have Miguel Cabrera's 0 for SB show because he is a hitter. Kershaw and Hernandez are pitchers so they need to have their stats show up blank. Let me know if you have any questions. Thanks!

[vlady]

so clayton anf felix cannot have a value/score in that table ( Column T - Column AD) coz they are pitchers right?

=IF(SUMPRODUCT(('Player Database'!$C$6:$C$15='Data Table'!$C13)*'Player Database'!$T$6:$AD$15)=0,"",IFERROR(INDEX('Player Database'!$AD$4:$AD$15,MATCH(C13,'Player Database'!$C$4:$C$15,0))," "))

this will sum up the rows in Columns T to Ad for specific person and if it return 0 will show blank.


...
Question how do you know if the player is pitcher or hitter?

[vlady]

so clayton anf felix cannot have a value/score in that table ( Column T - Column AD) coz they are pitchers right?

=IF(SUMPRODUCT(('Player Database'!$C$6:$C$15='Data Table'!$C13)*'Player Database'!$T$6:$AD$15)=0,"",IFERROR(INDEX('Player Database'!$AD$4:$AD$15,MATCH(C13,'Player Database'!$C$4:$C$15,0))," "))

this will sum up the rows in Columns T to Ad for specific person and if it return 0 will show blank.


...
Question how do you know if the player is pitcher or hitter?

[mlbdc2012]

so clayton anf felix cannot have a value/score in that table ( Column T - Column AD) coz they are pitchers right?

=IF(SUMPRODUCT(('Player Database'!$C$6:$C$15='Data Table'!$C13)*'Player Database'!$T$6:$AD$15)=0,"",IFERROR(INDEX('Player Database'!$AD$4:$AD$15,MATCH(C13,'Player Database'!$C$4:$C$15,0))," "))

this will sum up the rows in Columns T to Ad for specific person and if it return 0 will show blank.


...
Question how do you know if the player is pitcher or hitter?

The positions are listed in columns V:Y, anything that contains a P in it is a pitcher, so look at clayton and felix those are the only two pitchers on the list of 10 names.

[vlady]

so you can check it first like using if statement

say from your original =IFERROR(INDEX('Player Database'!$FB$12:$FB$1625,MATCH(C13,'Player Database'!$C$12:$C$1625,0)),"")
note that we are in row 13 so check row 13 column W for "P" - pitcher based on your sample file

=if(W13="P","",IFERROR(INDEX('Player Database'!$FB$12:$FB$1625,MATCH(C13,'Player Database'!$C$12:$C$1625,0)),""))

[mlbdc2012]

so you can check it first like using if statement

say from your original =IFERROR(INDEX('Player Database'!$FB$12:$FB$1625,MATCH(C13,'Player Database'!$C$12:$C$1625,0)),"")
note that we are in row 13 so check row 13 column W for "P" - pitcher based on your sample file

=if(W13="P","",IFERROR(INDEX('Player Database'!$FB$12:$FB$1625,MATCH(C13,'Player Database'!$C$12:$C$1625,0)),""))

What would it be if I wanted it to look up columns V:Y instead of just W in the beginning of the formula?

[vlady]

you mean if the "P" would be in any column of V, W or Y ?

you can use countif

=if(countif(V13:Y13,"P")>0,your index/match here,"")

[mlbdc2012]

you mean if the "P" would be in any column of V, W or Y ?

you can use countif

=if(countif(V13:Y13,"P")>0,your index/match here,"")

When I use that formula it gives me false. Here's the formula you sent me, let me know where it's off...

=IF(COUNTIF(V13:Y13,"P")>0,IFERROR(INDEX('Player Database'!$T$12:$T$1625,MATCH($C13,'Player Database'!$C$12:$C$1625,0)),""))

[vlady]

you missed the quotes at the end
the first quote belongs to the iferror function()
=IF(COUNTIF(V13:Y13,"P")>0,IFERROR(INDEX('Player Database'!$T$12:$T$1625,MATCH($C13,'Player Database'!$C$12:$C$1625,0)),""),"")

[mlbdc2012]

you missed the quotes at the end
the first quote belongs to the iferror function()
=IF(COUNTIF(V13:Y13,"P")>0,IFERROR(INDEX('Player Database'!$T$12:$T$1625,MATCH($C13,'Player Database'!$C$12:$C$1625,0)),""),"")

Now it's showing a blank for the first guy instead of 160 like it should for Mike Trout. Any ideas?

[vlady]

=IF(COUNTIF(V13:Y13,"P")>0,"",IFERROR(INDEX('Player Database'!$AD$4:$AD$1625,MATCH(C13,'Player Database'!$C$4:$C$1625,0))," "))

[mlbdc2012]

=IF(COUNTIF(V13:Y13,"P")>0,"",IFERROR(INDEX('Player Database'!$AD$4:$AD$1625,MATCH(C13,'Player Database'!$C$4:$C$1625,0))," "))

Perfect thank you!

[vlady]

Your very much welcome.

Regards,
Vlady