Formatting issue

rusty21

New Member
Joined
Dec 31, 2012
Messages
33
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

Well-known Member
Joined
Dec 30, 2008
Messages
6,899
Office Version
2019
Platform
Windows
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

New Member
Joined
Dec 31, 2012
Messages
33
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

Well-known Member
Joined
Dec 30, 2008
Messages
6,899
Office Version
2019
Platform
Windows
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

New Member
Joined
Dec 31, 2012
Messages
33
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
 

Forum statistics

Threads
1,077,822
Messages
5,336,564
Members
399,088
Latest member
Swindlestikz

Some videos you may like

This Week's Hot Topics

Top