IF function & formulas


         I don't seem to get passed where i am at with sport results spreadsheet, also not sure wether I am using the right argument. The statement below is as far as I get, it will not load the draw in row 5. I wish also to show a numerical net result in column G.                                          

Win 3. Lose 0, Draw 1, and to show accumulating sub totals for each round and team.


Below is first round of five descending and  one team of six across the page

CLUBS   C D E F G ROUNDS   RINKS SHOTS for SHOTS against W/L/D NET   SINGLES A2 25 17 3 8 1 PAIRS A1 10 20 0 -10   FOURS B6 18 18 1 0 SUB TOTAL   53 55 4 -2              

Post Edited
Title: Title was not descriptive.


Selected Answer

 The IF function consists of 3 parts as follows.

= IF ( [Argument] , [Argument is True], [Argument is False] )

Therefore this part of your formula = IF ( D3>E3, "3", "") is all that is needed and all that Excel can execute. Whatever follows must be ignored and therefore causes an error. Unfortunatley I can't figure out the description of your worksheet but I presume that you mean to nest the IFs, meaning

= IF ( [First argument], [True], [ELSE Second argument], [True], [ELSE Third argument], [True], [False] )))

The False result would occur only after all three arguments have been evaluated and none of them is True. That would leave you with this formula:-


Also, remember that Excel likes numbers, not text. 3 is a number, "3" is a word. I urge you to replace "3", "0" and "1" with 3, 0 and 1. The null string ("") as False result is also a string (text) and therefore unfortunate. It may cause an error if you wish to do calculations with it. If you come to that point it may help you to know that "" <> 0 but SUM("") = 0.

In response to the appended discussion I amend the formula to compare D3 and D4 in all three tests. I also modify the logic because it is assured that either D3 or D4 must be higher unless they are equal. The condition of having no result applies only if one of the parameters is missing (D3 or D4 being blank).

=IF(OR($D3="", $E3=""),"",IF($D3=$E3,1,IF($D3>$E3,3,0)))

Another way of doing the same thing is to use the SIGN() function which returns 1, 0 or -1 depending upon the sign (plus, minus or zero) of a number. Therefore SIGN(D3-E3)+1 would give you 2 for a win, 1 for a draw and 0 for a loss without the use of IF. But then you must use an IF to award the extra point for a win. The whole thing then looks like this:

=IF(OR($D3="", $E3=""),"",SIGN($D3-$E3) + IF($D3>$E3,2,1))

Paste eigher formula in F3 of your sheet and copy down.

The formula for your column G would be

. This will create a negative result in case of a loss. I think this is what you need.


the second argument returns 1 and not  0 when entered on spreadsheet?
how do I get a differential in column G?
so:-       D      E      F/w   G 
score:-  25    17     3       8
Jemz (rep: 4) Aug 20, '17 at 6:00 pm
If the formula returns 1 instead of the expected 0 this may have to do with the sequence of evaluation. I can't judge because the formula examines data in 3 rows while you supply only data for one which, in addition, I don't logically understand.
As for the "differential" I suggest you put this in a separate question. In this thread we should get the IF formula working properly. Then we can move on.
Variatus (rep: 813) Aug 20, '17 at 9:23 pm
As you can see I am a bit lost, I have tried to upload a portion of the spreadsheet but the grid does not show but comes out as only words and numbers .The sample below is  round one of a five round bowls tournament. Row three, singles, Row four pairs,,Row five fours. Columns D,E show the results of those games,
Column  F show 3 for a win, 0 for a lose and 1 for a draw. Column shows the differential of those games.

    CLUBS   C D E F G ROUNDS   RINKS SHOTS for SHOTS against W/L/D NET   SINGLES A2 25 17 3 8 1 PAIRS A1 10 20 0 -10   FOURS B6 18 18 1 0 SUB TOTAL   53 55 4 -2
Jemz (rep: 4) Aug 21, '17 at 2:01 am
How can I get files t oshow as posted? have tried csv and xls files .
Jemz (rep: 4) Aug 21, '17 at 2:11 am
I believe you can't insert a file into the post. You can only attach. You might prepare a special workbook showing only the problem. Meanwhile, I am beginning to udnerstand your reduced numbers. But why does your formula refer to rows 3, 4 and 5 if it is to set column F?
Variatus (rep: 813) Aug 21, '17 at 4:53 am
I don't Know!! maybe this where i am getting confused? also is the correct formula  being applied??
I have tried once more to attach a csv  file, hope this works out 
Jemz (rep: 4) Aug 21, '17 at 5:12 am
Sorry, I couldn't download your CSV file. I think it's a weak connection to the server and might try again at another time. If it is still relevant, why do you choose CSV over XLXS format? It's extra work for both of us.
Anyway, I think I got the hang of things now and modified my answer above. Please take a look.
Variatus (rep: 813) Aug 21, '17 at 5:53 am
Thanks will try your new argument, and get back to you.
Some where I read that CSV was the file format to use!!
Jemz (rep: 4) Aug 21, '17 at 6:13 am
 Have attached full sheet, in XLSX. both formulas work but remove grid lines as you scroll down?
Jemz (rep: 4) Aug 21, '17 at 7:40 pm
I'm glad you got the formulas working! I also managed to download your file now. :-)
There are several solutions to your remaining problem but from the point of view of this forum that is a different question. To benefit other visitors who may have the same problem please ask the question in a separate thread and I shall answer it if nobody else does.
Variatus (rep: 813) Aug 21, '17 at 9:54 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login