Ignore empty cells in an IF formula

moppyau

New member
Joined
May 6, 2013
Messages
4
Reaction score
0
Points
0
Location
Australia
Excel Version(s)
Excel2013
Hi All.
I've attached a spreadsheet that I'm having a bit of a problem with. The formula in J2 awards 4 points if St. Monicas defeats another team & awards 2 points in the case of a draw. My problem is that if there are no scores entered in columns D or F it awards 2 points as blank cells are counted as equal. Is it possible for excel to ignore cells that are empty in an IF formula?
Many thanks in advance
Mop
 

Attachments

  • Book1.xlsx
    15.8 KB · Views: 14

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
Hi
Ive worked extensively with data analysis, and I often wished that there wasn't so much special formatting/merged cells etc, which makes it difficult to develop formulae that would otherwise be quite simple.
In this case, I would add a helper column (G) to place H, D, or A according to the result, and change the formulae to SUMPRODUCT (*4) or (*2) to calculate the points. On the other hand, if you created two extra tables sorted (a) by home team and (b) by away team with no gaps you could include the points awarded and do it by simple addition.

HTH
 

moppyau

New member
Joined
May 6, 2013
Messages
4
Reaction score
0
Points
0
Location
Australia
Excel Version(s)
Excel2013
Thanks Hercules. I'll fiddle around with your suggestions. Just thought there would be a simple addition to the current formula. The example spreadsheet was a very basic version of the original. Once again thanks for your help. Much appreciated.
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
Hi Mop
Im assuming that the problem lies where your team table is built, but the scores aren't in, so that they are of equal value? I can't see a simple amendment to the IF statements because your processing an array of data, and you would need an extra IF to test for blanks within each +IF thats testing for equal is TRUE, so it will make it much longer. I still think that it might be easier if you added extra data as partr of your table to indicate Home, Away, or Draw instead of trying to do it with one formula, but without seeing the full spreadsheet, I don't know how much work would be required.
If I can get a bit of spare time today I will try using the SUMPRODUCT approach with your sample.

Will post further later
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
Hi Mop
Ive added an extra table to the example you posted (P1:S9) that does the calculations using the SUMPRODUCT function. If you set the scores to blank, it doesn't treat them as equal by adding 2.
The points formula is a bit lengthy, but once its constructed it can be copied down. In my example, Ive restricted the arrays to rows 1 to 34, which covers your Div1a teams. If you set this to cover your longest list of fixtures, then you can also copy the formulae across sheets (provided that they are laid out the same).

Hope that helps
 

Attachments

  • SumProduct.xlsx
    17.6 KB · Views: 11
Top