calculate scores based on 3 x conditions

Hi,

Hoping someone can help me here.
I am setting up a small league for a small club and would like Excel to calculate scores that match the following conditions.

1. Points for exact score = 3 pts
2. Points for correct goal difference = 2pts (i.e. predicted score = 2-2 and actual is 1-1, so difference = 0 so 2pts should be awarded; same goes as follows: predicted = 4-0 and actual = 6-2, so 2pts should be awarded as difference = 4)
3. Correct result = 1pt (home or away)

I have the above in a table on my 'settings' sheet as the points allocation may change next season

I looked around this site and Internet but I couldn't find something that matches my requirements. The closest formulas that I found are in my mock-up. They make use of the SIGN function which I have never came across in my time with Excel.

Can someone help me?

Thanks

Thanks for the reply.
The formulas in my workbook were just examples taken from the Internet - I played with the formuals a bit to try and make them fit my needs but I didn't get anywhere.

I have updated the workbook with expected actual scores

book1.xls

reddwarf,

I worked on your first formula according to the comments that you inserted. Let me know if that is what you wanted. If it is what you wanted, that can either get you started or I can work on the others when I have more time.

If it is not what you wanted, please be more descriptive about your goals.

Mark

book1.xls

mgaworecki:

Thanks for reply.
The comments in my sample workbook were used to describe the calculation of the formula which I found on the internet, and are not my requirement. My requirement is detailed in post#1, which is:

1.Points for exact score = 3 pts
2.Points for correct goal difference = 2pts (i.e. predicted score = 2-2 and actual is 1-1, difference = 0 so 2pts should be awarded; same goes as follows: predicted = 4-0 and actual = 6-2, so 2pts should be awarded as difference = 4)
3.Correct result = 1pt (home or away)