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


A

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
 
Ad

Advertisements

A

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
 
T

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
 
T

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
 
P

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
 
A

Ace Fekay [MVP]

In
Tyro said:
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
 
Ad

Advertisements

A

Ace Fekay [MVP]

In
Pete_UK said:
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
 
P

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
 
A

Ace Fekay [MVP]

In
Pete_UK said:
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
 
P

Pete_UK

Have you ever been approached about becoming an MVP?

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

Pete
 
Ad

Advertisements

A

Ace Fekay [MVP]

In
Tyro said:
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
 
Ad

Advertisements

T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Skip blanks 6
skipping blanks? 3
Non-numeric Data 12
skipping blank spaces 1
function for last non-blank entry in column? 2
Average Formula 2
Average Formula 4
Formula to remove non-numeric characters from a cell 2

Top