Formula to Average non-numeric entries and skip if blank, and more

[Ace Fekay [MVP]]

I have the following dataset for a bar pool league (using Excel 2007):

Cell# and Header:
-----------------------------------
A (TeamPlayed/Date)
B(Team Played/Date)
....
P(Christmas Break)
Q(TeamPlayed/Date)
R(TeamPlayed/Date)
....
AD (TeamPlayed/date) (last game in the season)


Data (H=home, A=Away)
-----------------------------------
WH=Win at Home
WA=Win Away
LH=Loss at Home
LA=Losst Away
0=No show


At the end of a row (player), I am trying to create these stats:
-----------------------------------
AE (Total Possible # of Matches in the season)
AF (Total Games Played)
AG (Dedication Percentage) = AH + AI / AE
AH (# of Wins) = WH + WA
AI (# of Losses) = LH + LA
AJ (Total Wins Percentage) = WH + WA/ AF
AK (Wins at Home Percentage) = WH / AF
AL (Wins Away Percentage) - WA / AF

btw - FYI if interested, the Dedication Percentage is just to throw a bone
and a pat on the back for the folks that have a better track record showing
up more than some others! So for example if they showed up for every match
whether a WH, WA, LH or LA L, it should be 100% dedication, which assumes no
cell has a 0. In some cases someone may show but not play, but I will leave
this one out instead of further complicating this mess. Besides I don't
think the team captain kept track of this one anyway.

I've created charts a few years ago with stock market data showing moving
averages, medians, trends, etc, but I can't for the life of me to come up
with formulas to evaluate non-numerics in each cell.

I hope this is not too much to ask. I'm sure with someone with Excel
experience, I assume this should be a walk in the park.


TIA

--
Regards,
Ace

This posting is provided "AS-IS" with no warranties or guarantees and
confers no rights.

Ace Fekay, MCSE 2003 & 2000, MCSA 2003 & 2000, MCSE+I, MCT,
MVP Microsoft MVP - Directory Services
Microsoft Certified Trainer

For urgent issues, you may want to contact Microsoft PSS directly. Please
check http://support.microsoft.com for regional support phone numbers.

Infinite Diversities in Infinite Combinations

 

[Ace Fekay [MVP]]

In Ace Fekay [MVP] <[email protected]> typed:


Or should I just leave the no shows blank instead of 0? I don't know which
will be easier.

Thanks again,

Ace

 

[Tyro]

The average function ignores text, logical and blank (empty) cells but
includes cells with 0. That may be a simple solution for you.

Tyro

 

[Tyro]

I should have also added you can use AVERAGEIF in Excel 2007:
=AVERAGEIF(Range,"<>0") to exclude 0's in the range
Also in all Excel versions: =AVERAGE(IF(Range<>0,Range)) entered as an array
formula (CTRL+SHIFT+ENTER, not just ENTER)

Tyro

 

[Pete_UK]

Assuming your data is on row 2, try these:

AE2: =COUNTA(A2:AD2)
AF2: =COUNTIF(A2:AD2,"W*")+COUNTIF(A2:AD2,"L*")
AG2: =(AH2+AI2)/AE2
AH2: =COUNTIF(A2:AD2,"W*")
AI2: =COUNTIF(A2:AD2,"L*")
AJ2: =AH2/AF2
AK2: =COUNTIF(A2:AD2,"WH")/AF2
AL2: =COUNTIF(A2:AD2,"WA")/AF2

Format the percentage cells as percentage, i.e. the last 3 and AG2.

Hope this helps.

Pete

 

[Ace Fekay [MVP]]

In

I should have also added you can use AVERAGEIF in Excel 2007:
=AVERAGEIF(Range,"<>0") to exclude 0's in the range
Also in all Excel versions: =AVERAGE(IF(Range<>0,Range)) entered as
an array formula (CTRL+SHIFT+ENTER, not just ENTER)

Tyro

That formula appears to be for numerics, unless I'm missing something?

Ace

 

[Ace Fekay [MVP]]

In

Assuming your data is on row 2, try these:

AE2: =COUNTA(A2:AD2)
AF2: =COUNTIF(A2:AD2,"W*")+COUNTIF(A2:AD2,"L*")
AG2: =(AH2+AI2)/AE2
AH2: =COUNTIF(A2:AD2,"W*")
AI2: =COUNTIF(A2:AD2,"L*")
AJ2: =AH2/AF2
AK2: =COUNTIF(A2:AD2,"WH")/AF2
AL2: =COUNTIF(A2:AD2,"WA")/AF2

Format the percentage cells as percentage, i.e. the last 3 and AG2.

Hope this helps.

Pete

Perfect! It took awhile, but I finally got all the data in, double checked
it, etc, and put those formulas in adjusting for my starting row, and it
worked nicely!

I stil have some things to work out. Some of the 0's are also for forfeits,
which I forgot about, so it doesn't really show true 'dedication.' and it
would be too complicated to figure it out and enter it. I might change the
name to 'overall percentage *if* you showed up every match with your current
stats. So if a player showed up more and played, they would at least have a
win or loss to increase their percentage.

I actually have two worksheets. Sheet 1 are single matches, and Sheet two
are for doubles matches. I am trying now to figure out how to combine them
on Sheet 3 without duplicate rows for each player, for overall stats. I'll
play around with it a bit and see what I come up with.

I thank you for taking the time to work this out with, especially with my
own cell references. It was great!

Ace

 

[Pete_UK]

You're welcome, Ace - thanks for the feedback. It's good to be able
to help an MVP, as they usually help us !! <bg>

Pete

 

[Ace Fekay [MVP]]

In

You're welcome, Ace - thanks for the feedback. It's good to be able
to help an MVP, as they usually help us !! <bg>

Pete

I appreciate it and reciprocate the effort, if I can. If you need any help
in AD, DNS or Exchange, it would be a pleasure to help. I enjoy helping
folks. Share the knowledge!

Have you ever been approached about becoming an MVP?

Ace

 

[Pete_UK]

Have you ever been approached about becoming an MVP?

No, but I live in hope !! <bg>

Pete

 

[Tyro]

Yes. The AVERAGE function averages numbers..

Tyro

 

[Ace Fekay [MVP]]

In

Yes. The AVERAGE function averages numbers..

Tyro

Ok, thought so. I've used that function before but unfortunately I have
non-numerics and couldn't figure out how to get it to work and examine each
cell to create a condition based on the content of the cell.

Cheers!

Ace

 

[Ace Fekay [MVP]]

In

No, but I live in hope !! <bg>

Pete

Replied privately.

 

[Tyro]

The AVERAGE function's syntax is =AVERAGE(arg1,arg2,...argn). See Excel help
for more info.

a.. Logical values and text representations of numbers that you type
directly into the list of arguments are counted.
a.. If a range or cell reference argument contains text, logical values, or
empty cells, those values are ignored; however, cells with the value zero
are included.

Tyro