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,901
Messages
5,361,937
Members
400,666
Latest member
UDLearning

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top