Quick Excel question - Fantasy Baseball Cafe 2015 Fantasy Baseball Cafe
100% Deposit Bonus for Cafe Members!

Return to General Talk

Quick Excel question

Moderator: Baseball Moderators

Quick Excel question

Postby Webster11 » Thu Jan 26, 2006 9:57 pm

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?
Image
Webster11
Major League Manager
Major League Manager

User avatar

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

Re: Quick Excel question

Postby RugbyD » Thu Jan 26, 2006 10:06 pm

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
Hall of Fame Hero

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

Postby acsguitar » Thu Jan 26, 2006 10:59 pm

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

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

Re: Quick Excel question

Postby JTWood » Thu Jan 26, 2006 11:22 pm

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... O:-)
Image
JTWood
Hall of Fame Hero
Hall of Fame Hero

User avatar
CafeholicCafe WriterEagle EyeWeb Supporter
Posts: 11508
Joined: 22 Jun 2004
Home Cafe: Baseball
Location: Unincorporated Heaven

Re: Quick Excel question

Postby RugbyD » Thu Jan 26, 2006 11:33 pm

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... O:-)

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
Hall of Fame Hero

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

Re: Quick Excel question

Postby JTWood » Thu Jan 26, 2006 11:35 pm

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... O:-)

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.

;-7 :-b
Image
JTWood
Hall of Fame Hero
Hall of Fame Hero

User avatar
CafeholicCafe WriterEagle EyeWeb Supporter
Posts: 11508
Joined: 22 Jun 2004
Home Cafe: Baseball
Location: Unincorporated Heaven

Postby roadrunner » Sat Jan 28, 2006 3:34 am

this could not be more Greek
roadrunner
College Coach
College Coach

User avatar

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


Return to General Talk

Who is online

Users browsing this forum: No registered users and 5 guests

Forums Articles & Tips Sleepers Rankings Leagues


  • Fantasy Baseball
  • Article Submissions
  • Privacy Statement
  • Site Survey 
  • Contact