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
7,149
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
7,149
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,081,726
Messages
5,360,906
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top