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 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

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...

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.

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.