Formula not working correctly when a cell is being populated by another formula

[gazzak]

Hi All,

Can't work this one out so looking for some advice, (or more accurately for someone to tell me what idiotic thing I'm doing)!

So I have a formula for a football, (soccer), prediction sheet. This compares the predicted result to the actual result and awards a set number of points based on the comparison. It goes wrong when a number is entered in either B5 or C5 that isn't directly input by myself, IE a formula generates the number. This only fails on the last part of the formula below.

For example:

B5=actual home team score, C5=actual away team score, F5=predicted home teams core, G5=predicted away team score

=IF(AND(B5=C5,B5=F5,B5=G5),4,IF(B5&C5=F5&G5,3,IF(AND(B5=C5,F5=G5),1,IF(AND(B5>C5,F5>G5),1,IF(AND(B5<C5,F5<G5),1,IF(OR(B5=F5,C5=G5),2,0))))))

Correct draw prediction for both teams = 4 points: IF(AND(B5=C5,B5=F5,B5=G5),4
Not a draw and correct score prediction for both teams = 3 points: IF(B5&C5=F5&G5,3
Home win with home win predicted but not correct scorw = 1 point: IF(AND(B5>C5,F5>G5),1
Away win with away win predicted but not correct score = 1 point: IF(AND(B5<C5,F5<G5),1
Incorrect guess but got 1 of the teams goals correct = 2 points: IF(OR(B5=F5,C5=G5),2 - this is what fails with formula generated numbers
Anything else is 0 points

So as long as I enter actual numbers in cells B5 and C5 this works great, but as soon as I set B5 and C5 to automatically produce the numbers the last part of the formula fails, IF(OR(B5=F5,C5=G5),2

The rest of the formula works fine no matter what I do with the cells, just that last bit!

I have now spent waaay to long on this so need some advice. Cheers

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

Are you really still using Excel 2003?

[gazzak]

Hi Ali,

Demo excel sheet attached. Thanks for the heads up.
I'm using a later version of Excel but I'm a very rusty and old school user .

Cheers

[AliGW]

Which version are you using? Please update your forum profile accordingly. It may well be significant.

[AliGW]

You are trying to compare numbers with text.

Change formulae thus:

=--LEFT(E8)

=--RIGHT(E8)

[gazzak]

Wow that's amazing. What difference does the -- make?

[AliGW]

It turns text into a number.

I asked you to update your profile - please do so now.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[gazzak]

Yep I'll do all of that. Thanks for the help!