Libre Calc "=IF" function is not returning correct results

English
#1

Hi…
I am making a spreadsheet for a sport competition which will automatically display the winners of a game by comparing the scores of the players… the simple formula used is:

IF(playeronescore<playertwoscore, playertwo, player one)

(Thus, it compares the score of the two players, and if player one has the lower score, it returns the name “player two”, and of course, “player one” if their score ISN’T lower…)

I had previously had to reverse the “<” symbol to “>” in order to get the correct results, but to illustrate the difficulty, I have now returned all formulae to their ‘correct’ status to show the issue…

Can anyone explain why this is happening and how I resolve it?

I hope to be able to attach an image to show this:

Ignore column A… columns D and I are duplicates of the score entered in the merged cells in column E and F. Column J shows the formula used (but not as a formula), and Column K shows the true result of the formula…

So… looking in Row 3, the formula states: "If Melissa’s score (D3) is LESS THAT Chris P’s score (I3), then respond “Chris P”, but if not, return “Melissa”
We can see that Melissa’s score ISN’T Less than… so it should return “Melissa”, you can see it doesn’t!

Now compare the game in rows 9-11 with the game in rows 12-14… although the formula is the same, suddenly it is returning the correct result in the 12-14 game…

Any ideas!!!

Spreadsheet problem
Spreadsheet problem749×358 114 KB

#2

Likely your values are of type Text instead of Numeric, so 13 compares less than 7.
See this FAQ for how to convert text to numeric.

1 Like
#3

That is a possibility that I had considered so made sure they were all numeric… I’ll check again in the morning… nut doesn’t explain why it suddenly works properly half way down the sheet…

#4

In row 12: 11 is less than 13 (although text), the same that aa is less than ac. Thinks as if do you need to sort them.

#5

You might have to click View > Value highlighting (Ctrl+F8) to see if they are numbers (blue) or text (black).
Screenshot Reader gives following spreadsheet so obviously I can’t see what format any of the cells are. I copied J3 to L3 with an = in front and dragged down; it seems OK.
IF_Function93113.ods (17.5 KB)

#6

I tried your suggestion of “value highlighting”… the entire spreadsheet comes up green, except column 1 (Lane number which I entered manually) which was blue, and columns E,F (Where I entered the score manually) which is black. I tried to force all columns to numeric, AND text, and nothing seemed to change the colour shown on Highlight values.
I also tried to copy the formula across as you have done, into column L and this too returned an incorrect result as before

#7

YAY!!! Some success!!!

Curious, but I highlighted the score columns E,F and it did nothing to change the colours of the cells concerned… or the results…
BUT… I went into an individual cell and changed it to “numeric”, still no change, but THEN re-entered the number into the cell, when it became blue and gave the correct result after!!!

Now I have to do it manually for all the cells one at a time!!

Seems to be ok for now though… many thanks AI

#8

See the link in @erack’s answer above for a better method.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.