Only Calculating Numerics

[JackT91]

Hi

I'm setting up a spreadsheet for the fixtures/results/points table for
league so that all i have to do is enter the scores of each match and i
will calculate the wins/draws/losses for each team and then calculat
the points based on those wins/draws/losses.

I've managed to do the formulas for wins, losses and points, but i'
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, becaus
technically they are equal.

So what i would like to know is; is there a way i can set this formul
up so that it will only take into account the numerical values for th
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 point
table so the team with the most points is at the top and sorts itsel
when the points change? Even though every cell on those rows hav
formulas(except for the team names).


Thanks in advance! :

 

[Spencer101]

Hi

I'm setting up a spreadsheet for the fixtures/results/points table for
league so that all i have to do is enter the scores of each match and i
will calculate the wins/draws/losses for each team and then calculat
the points based on those wins/draws/losses.

I've managed to do the formulas for wins, losses and points, but i'
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, becaus
technically they are equal.

So what i would like to know is; is there a way i can set this formul
up so that it will only take into account the numerical values for th
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 point
table so the team with the most points is at the top and sorts itsel
when the points change? Even though every cell on those rows hav
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 i
a 0-0, 1-1 etc. draw it will populate with 1 point.

For sorting the table automatically you will probably need to use som
VBA. Not my strong point I'm afraid so I will leave that part of th
question for someone more qualified.

Hope that helps.

S

 

[Spencer101]

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

Because there are three games being played by the one team, i have se
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 hel
you out with the full formula

 

[Ron Rosenfeld]

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.

 

[JackT91]

;1602567']
I would suggest either posting a comprehensive example here, or postin
a sample workbook on a publicly shared website and posting the lin
here.

Waiting for my post to be okayed :

 

[Vacuum Sealed]

;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.