# If 2 results don't match, how can I make a 'Total' cell show 0, right now it shows 2 or 5

1. ## If 2 results don't match, how can I make a 'Total' cell show 0, right now it shows 2 or 5

Hi all,

I have a score predictor game in a spreadsheet (see attached), and I have a formula in to work the points out already e.g. if the actual score is 1-1 and the person predicted the correct score exactly (e.g. 1-1), then they get 5 points, if they predicted the correct result (e.g. 2-2) then they get 2 points, or if they predicted in this case, a win for either time, then they would get 0 points.

My problem is that I have entered people's predictions (please see Sheet1, table titled Week 2 - I have entered 'Zak W' and 'Luke B' score predictions) and the games haven't been played yet so the actual results are blank. However, the total points adds up to 2 whenever someone has predicted a score draw, or if someone has predicted 0-0, it automatically gives them 5 points. How can I make these points stay at '0' until I have entered the actual results as well e.g. something along the lines of 'if actual scores blank, points = 0'.

Here is my current formula which works minus the problem I have:
Formula:
`Please Login or Register  to view this content.`

Hopefully I made sense and I look forward to any assistance.

Thank you so much, Zak

2. ## Re: If 2 results don't match, how can I make a 'Total' cell show 0, right now it shows 2 o

Like this:
=IF(OR(actual scores = "",(D16>E16)-(F16>G16),(D16<E16)-(F16<G16)),0,IF(AND(D16=F16,E16=G16),5,2))

3. ## Re: If 2 results don't match, how can I make a 'Total' cell show 0, right now it shows 2 o

Hi Paul, thanks for your quick response. I tried playing with that and couldn't make it work. I probably sound really stupid now, but I tried replacing actual scores with D16,E16 to show the boxes that if blank, then = 0 but it comes up with #NAME? or when I thought I got it to work, it lost the formula for making it 2/5 points with correct result/score.

Any tips?

Many thanks again

4. ## Re: If 2 results don't match, how can I make a 'Total' cell show 0, right now it shows 2 o

Part of your problem is that you typed in "-" instead of just leaving the cells empty.
In H16, etc, use:
``Please Login or Register  to view this content.``

5. ## Re: If 2 results don't match, how can I make a 'Total' cell show 0, right now it shows 2 o

Ah yes that formula works much better, thank you! The only thing, it's not making the Points box show '0', it's showing blank which is affecting my totals making them show #VALUE! because the boxes have no numbers in...I don't see in the formula you gave me what would stop it filling a 0 into the box...hmmm

6. ## Re: If 2 results don't match, how can I make a 'Total' cell show 0, right now it shows 2 o

``Please Login or Register  to view this content.``

There are currently 1 users browsing this thread. (0 members and 1 guests)