Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Only Calculating Numerics
Hi :)
I'm setting up a spreadsheet for the fixtures/results/points table for a league so that all i have to do is enter the scores of each match and it will calculate the wins/draws/losses for each team and then calculate the points based on those wins/draws/losses. I've managed to do the formulas for wins, losses and points, but i'm struggling with the draws. The formula i'm using for draws is: =IF(E4=E3,1,0) It's adding 1 to a draw for each of the blank results, because technically they are equal. So what i would like to know is; is there a way i can set this formula up so that it will only take into account the numerical values for the scores, so it will only add 1 to draws when there is an actual score? Also, while i have your attention, is there a way i can sort the points table so the team with the most points is at the top and sorts itself when the points change? Even though every cell on those rows have formulas(except for the team names). Thanks in advance! :) |
#2
|
|||
|
|||
Quote:
Hi, Try using =IF(AND(E4="",E3=""),"",IF(E4=E3,1,0)) This will show no result if both E4 and E3 are empty. Then if there is a 0-0, 1-1 etc. draw it will populate with 1 point. For sorting the table automatically you will probably need to use some VBA. Not my strong point I'm afraid so I will leave that part of the question for someone more qualified. Hope that helps. S. |
#3
|
|||
|
|||
Quote:
Because there are three games being played by the one team, i have set three conditions in the one cell. It goes like this: =IF(E4=E3,1,0)+IF(H4=H3,1,0)+IF(E6=E7,1,0) I tried editting it to what you suggested, and i get a #VALUE error :\ |
#4
|
|||
|
|||
Quote:
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Only Calculating Numerics
On Sun, 10 Jun 2012 10:38:10 +0000, JackT91 wrote:
Also, while i have your attention, is there a way i can sort the points table so the team with the most points is at the top and sorts itself when the points change? Even though every cell on those rows have formulas(except for the team names). You could use an event-triggered macro to do that. The details of how to set that up depend on the details of your worksheet, which you have not yet chosen to share with us. I would suggest either posting a comprehensive example here, or posting a sample workbook on a publicly shared website and posting the link here. |
#6
|
|||
|
|||
Waiting for my post to be okayed :(
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Only Calculating Numerics
On 11/06/2012 6:19 AM, JackT91 wrote:
'Ron Rosenfeld[_2_ Wrote: ;1602567'] I would suggest either posting a comprehensive example here, or posting a sample workbook on a publicly shared website and posting the link here. Waiting for my post to be okayed :( G'day Jack If you & or your next level tier management feel this workbook may contain sensitive information, simply save a copy and replace it with some bogus details. I'm fairly confident in saying that all the contributors here, who give freely of their talents and their time, have little or no interest in the purpose of the book itself, as soon as you can post the details the sooner we can nut out your problem and move onto another poster who requires help. I myself Post Help question relating to my employers needs for specific workbook requirements, & in return I answer as many posts I can that fit within my region of knowledge. Cheer. Mick. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete all non-numerics | Excel Programming | |||
Extract Numerics only | Excel Discussion (Misc queries) | |||
VALIDATION USING ALPHA-NUMERICS ONLY | Excel Programming | |||
Remove Non-Numerics from String | Excel Programming | |||
How to forecast non-numerics? | Excel Worksheet Functions |