 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2008Formula to Average non-numeric entries and skip if blank, and more
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  SignatureRegards, 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 Or should I just leave the no shows blank instead of 0? I don't know which will be easier. Thanks again, Ace The average function ignores text, logical and blank (empty) cells but includes cells with 0. That may be a simple solution for you. Tyro >I have the following dataset for a bar pool league (using Excel 2007): > [quoted text clipped - 46 lines] > > TIA 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 > The average function ignores text, logical and blank (empty) cells but > includes cells with 0. That may be a simple solution for you. [quoted text clipped - 51 lines] >> >> TIA > 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 Yes. The AVERAGE function averages numbers.. Tyro >> I should have also added you can use AVERAGEIF in Excel 2007: >> =AVERAGEIF(Range,"<>0") to exclude 0's in the range [quoted text clipped - 6 lines] > > Ace > 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 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 >> Yes. The AVERAGE function averages numbers.. >> [quoted text clipped - 7 lines] > > Ace 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 On Apr 14, 11:57 pm, "Ace Fekay [MVP]" <PleaseAs...@SomeDomain.com> wrote: > I have the following dataset for a bar pool league (using Excel 2007): > [quoted text clipped - 61 lines] > > Infinite Diversities in Infinite Combinations > Assuming your data is on row 2, try these: > [quoted text clipped - 12 lines] > > 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 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 On Apr 15, 6:09 am, "Ace Fekay [MVP]" <PleaseAs...@SomeDomain.com> wrote: > Innews:202e154e-2206-4acf-9962-2176ca999021@x41g2000hsb.googlegroups.com, > Pete_UK <pashu...@auditel.net> typed: [quoted text clipped - 38 lines] > > - Show quoted text - > 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 > Have you ever been approached about becoming an MVP? No, but I live in hope !! <bg> Pete > > Have you ever been approached about becoming an MVP? > > No, but I live in hope !! <bg> > > Pete Replied privately. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.