Formatting issue

[rusty21]

Hi all,

I have created a table for the football team I run to show goals scored in first half, second half and complete game (in a win/lose/draw format) as I want to see if there are any patterns in the data and also have linked to an overview chart for each.

The first half and second half work fine, however the full time score is returning 0 for all games at present because we have yet to play in the cell for the full time score I have a simple sum formula adding the first and second half scores.

I have the following formula in the win/lose/draw cell.

=IF(COUNT(AA25:AA26)=2,IF(AA25>AA26,"W",IF(AA25<AA26,"L","D")),"")

This is then conditionally formatted with red amber green based on W/L/D return

Is there any way that I can keep the sum function and the the formatting or will i have to put the score in anually?

Thanks in advance
Rusty

 

[jasonb75]

You must be entering manually at some point, the count refers to a sum, the sum will refer to other cells where you enter the score. Those are the cells that you need to do a count check on, not hte ones with the sum formula.

 

[rusty21]

I enter the opposition scores manually ours are a sum off the individual goal scorers. It is the opposition full time score that I have referred to as a sum (1st half + 2nd half). If I keep this as a manual input it works.

I was hoping to be able to keep the sum and get the formatting working.

If not possible then I will have to keep it as manual along with the 1st and 2nd half scores.

Regards
Rusty

 

[jasonb75]

Is there anything that you enter manually to separate games that have been played with a 0-0 draw from those that have not yet been played?
If there is then you should be able to check that with the formula in place of the count.

 

[rusty21]

Nothing entered for a nil nil draw. The cells are left empty and it works fine. Last season I didn't have the second half score on 1st and final score.

As I have added 2nd half score and used a sum formula to calculate if there is nothing in the box it comes back as a 0-0 and then the formatting puts it at a draw. If I take the sum function out and put in manually it works fine.

I think I will just do it manually as I can not find a way for it to work with auto sum.

Rusty