Quick Excel question - Fantasy Baseball Cafe 2015

## Quick Excel question

Moderator: Baseball Moderators

### Quick Excel question

Im working on analyzing stats and creating auction values through excel. I ran into a problem while trying to calculate average though. I did hits/ab and created a new column with that function. It gave me an error though and Im assuming it's because for a few of the values I'm dividing by 0. Is there an easy way to fix this, or should I just delete all the players with 0 abs?
Webster11
Major League Manager

Posts: 1110
Joined: 8 Apr 2005
Home Cafe: Baseball

### Re: Quick Excel question

Webster11 wrote:Im working on analyzing stats and creating auction values through excel. I ran into a problem while trying to calculate average though. I did hits/ab and created a new column with that function. It gave me an error though and Im assuming it's because for a few of the values I'm dividing by 0. Is there an easy way to fix this, or should I just delete all the players with 0 abs?

nope, can't bend the rules of algebra, but you can enter this:

=if(AB=0,"",H/AB)

of course use the cell numbers instead of H and AB
TennCare rocks!!!!
RugbyD
Hall of Fame Hero

Posts: 5591
Joined: 7 Dec 2004
Home Cafe: Baseball
Location: punting small dogs and being surly

Rugy needs an icon that means excel genious
I'm too lazy to make a sig at the moment
acsguitar
Hall of Fame Hero

Posts: 26722
Joined: 7 Apr 2004
Home Cafe: Baseball
Location: Back in General Talk WOOO!!!

### Re: Quick Excel question

RugbyD wrote:
Webster11 wrote:Im working on analyzing stats and creating auction values through excel. I ran into a problem while trying to calculate average though. I did hits/ab and created a new column with that function. It gave me an error though and Im assuming it's because for a few of the values I'm dividing by 0. Is there an easy way to fix this, or should I just delete all the players with 0 abs?

nope, can't bend the rules of algebra, but you can enter this:

=if(AB=0,"",H/AB)

of course use the cell numbers instead of H and AB

Rugby's suggestion is dead on, but if I'm calculating BA like that, I would modify his formula just a smidge:

=if(AB=0,AB,H/AB)

That way the output will be consistent in both format and appearance. It also won't screw with your sorts.

Again, that's just me. Not trying to step on Rugby's suggestion or anything...
JTWood
Hall of Fame Hero

Posts: 11508
Joined: 22 Jun 2004
Home Cafe: Baseball
Location: Unincorporated Heaven

### Re: Quick Excel question

JTWood wrote:
RugbyD wrote:
Webster11 wrote:Im working on analyzing stats and creating auction values through excel. I ran into a problem while trying to calculate average though. I did hits/ab and created a new column with that function. It gave me an error though and Im assuming it's because for a few of the values I'm dividing by 0. Is there an easy way to fix this, or should I just delete all the players with 0 abs?

nope, can't bend the rules of algebra, but you can enter this:

=if(AB=0,"",H/AB)

of course use the cell numbers instead of H and AB

Rugby's suggestion is dead on, but if I'm calculating BA like that, I would modify his formula just a smidge:

=if(AB=0,AB,H/AB)

That way the output will be consistent in both format and appearance. It also won't screw with your sorts.

Again, that's just me. Not trying to step on Rugby's suggestion or anything...

good call, so long as the cell result isn't being used to compute an average value with other cells. if so it will count the 0 if its there, but a blank won't factor in the calculation.
TennCare rocks!!!!
RugbyD
Hall of Fame Hero

Posts: 5591
Joined: 7 Dec 2004
Home Cafe: Baseball
Location: punting small dogs and being surly

### Re: Quick Excel question

RugbyD wrote:
JTWood wrote:
RugbyD wrote:
Webster11 wrote:Im working on analyzing stats and creating auction values through excel. I ran into a problem while trying to calculate average though. I did hits/ab and created a new column with that function. It gave me an error though and Im assuming it's because for a few of the values I'm dividing by 0. Is there an easy way to fix this, or should I just delete all the players with 0 abs?

nope, can't bend the rules of algebra, but you can enter this:

=if(AB=0,"",H/AB)

of course use the cell numbers instead of H and AB

Rugby's suggestion is dead on, but if I'm calculating BA like that, I would modify his formula just a smidge:

=if(AB=0,AB,H/AB)

That way the output will be consistent in both format and appearance. It also won't screw with your sorts.

Again, that's just me. Not trying to step on Rugby's suggestion or anything...

good call, so long as the cell result isn't being used to compute an average value with other cells. if so it will count the 0 if its there, but a blank won't factor in the calculation.

I love you, too, man.

JTWood
Hall of Fame Hero

Posts: 11508
Joined: 22 Jun 2004
Home Cafe: Baseball
Location: Unincorporated Heaven

this could not be more Greek
College Coach

Posts: 152
Joined: 4 Oct 2004
Home Cafe: Baseball
Location: desert-ed