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! :)

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.

Hmm.. That's giving me a value error.

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 :\

If you can post an example worksheet it would make it far easier to help you out with the full formula.

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.

Waiting for my post to be okayed :(

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.