Excel 2007 - IF function to return a value only if a number is in both other cells
mattyl149
Posts: 2,110 Forum Member
✭✭✭
I've created a World Cup 2014 predictions spreadsheet for work. The tables update automatically with the numbers of wins, draws and losses, and I have a macro to update the tables
What is annoying me, and I can't seem to resolve is when both score cells are blank for a match, it's picking that up in the IF function as a draw
I've got it set up:
Team - Score - (Hidden columns for) Home Win (If formula to work out if it's a home win - Draw (If formula again) - Home Loss (If formula)
Then:
Score - (Hidden columns for) Home Win (If formula to work out if it's a home win - Draw (If formula again) - Home Loss (If formula) - Team
I've assigned values so that the wins in the hidden columns are a value of 3 or 0, the draws 1 or 0 and the losses 0 or 2. There's some reason why I used 2 (probably to differentiate it)
The win or loss values are based on there being one team scoring more or less goals than the other team in the match. The draw for when the values are the same, but it's treating the two blank cells as the same, and so treating it as a draw
Is there anyway that I can get the IF function to assign the value of 1 for a draw only when a score is entered for both teams, and not just because both cells happen to be blank
This is probably a bit of a convoluted explanation, and I can upload a copy of the spreadsheet somewhere if it helps
What is annoying me, and I can't seem to resolve is when both score cells are blank for a match, it's picking that up in the IF function as a draw
I've got it set up:
Team - Score - (Hidden columns for) Home Win (If formula to work out if it's a home win - Draw (If formula again) - Home Loss (If formula)
Then:
Score - (Hidden columns for) Home Win (If formula to work out if it's a home win - Draw (If formula again) - Home Loss (If formula) - Team
I've assigned values so that the wins in the hidden columns are a value of 3 or 0, the draws 1 or 0 and the losses 0 or 2. There's some reason why I used 2 (probably to differentiate it)
The win or loss values are based on there being one team scoring more or less goals than the other team in the match. The draw for when the values are the same, but it's treating the two blank cells as the same, and so treating it as a draw
Is there anyway that I can get the IF function to assign the value of 1 for a draw only when a score is entered for both teams, and not just because both cells happen to be blank
This is probably a bit of a convoluted explanation, and I can upload a copy of the spreadsheet somewhere if it helps
0
Comments
Therefore a nested IF works (Google them)
C1=IF(AND(A1>=1,B1>=1),IF(A1=B1,1,0),0)
returns 1 if a draw, 0 if not. One and One is also a draw.
Edit: 0,0 doesn't work
0,0 works now.
One ,One no longer works
=IF(AND(NOT(ISBLANK(A1)),(A1=B1)),1,0)
or for completeness
=IF(AND(NOT(ISBLANK(A1)),NOT(ISBLANK(B1)),(A1=B1)),1,0)
so the test is if a1 is not blank and a1=a2
or
so the test is if a1 and b1 are not blank and a1=a2, which shouldn't be necessary, but you never know.
i have a theory that people that ask for help with excel simply never come back to say thanks.
this does work.
=IF(AND(ISNUMBER(A1),ISNUMBER(B1),(A1=B1)),1,0)
Great. This works. Thanks very much
Can I prove you wrong?
My only other problem now, which I didn't consider, was the knockout stages
People are predicting the knockout results based on their results from the group stages. So for instance, the second round fixture Winner Group A vs Runner Group B is based on who they think will finish in those positions, not who actually finishes there
The more teams they predict correctly, the more points they score, and if those two teams happen to play together then they get points if their result is correct
I completely forgot about penalties. I need to set up a summary sheet to work out all the points, but that's not important. What I need to do is have a box underneath each fixture that people can put who they think will win the penalty shootout, if it goes to penalties. The fixtures for the next knockout stage, ie, the quarter-finals will either pick up the team that's scored more goals from the previous round, or if it was a draw, the team that the person thought would win on penalties
If it's a draw at 90 minutes and there's a winner after extra time, then I'll use that as the main score
Though doing logic contortions in Excel is fun.
It is fun, and I find most people can understand Excel better than Access
Apart from that I use Access all day long and like some variety
In such cases I create user defined functions which allow you to use the Excel version of Visual Basic. You can then use CASE, IF THEN... ELSE etc and create a program that it is easier to read and debug.
You basically call up the UDF like any other function with parameter values that can be used by the code you write.
Of course if you're not familar with Visual Basic...
Yes, and some small projects grow and grow until they get way out of control/depth.
I have come across way too many disasters in my time.
I've built more complicated spreadsheets. I've been using Excel for as long as I can remember, but there is still a lot that I haven't explored in it
cp2 mentioned Visual Basic, but I'm not really that familiar with it to be able to use it
I probably spent longer building this thing than I wanted. If anybody does want a copy (and don't expect anything too flashy) then I'm happy to send it to you
If you want to learn VB, learn it where a project will be structured, that being Access.
I'm not sure people care how you devise it as long as you get the right result.
Most spreadsheets I have come across are poorly designed and very hard to maintain. Ironically our IT frown on using databases but don't give a shit about the absolutely useless spreadsheets created across the organisation.
I work in MI for a bank and know all too well about poorly designed spreadsheets. We're changing the way we do our reporting so no doubt I'll be designing something that has to do many things. We're kind of limited with Access and the 2gb database size limit and have certain processes split over multiple databases. Which is partly why we're changing what we doing
This World Cup spreadsheet was more of a pet project. I like creating things whether it be a meme, a doctored picture or a spreadsheet, I like to keep my creative juices flowing. I always have a moment of inspiration