IF statement returning incorrect value

[CDelSignore]

I'm in an NFL pool and use Excel to keep track of the scores. I also use it to experiment and improve my Excel proficiency. I have come across what I perceive to be an error. The beginning of the code, I look to see if a row# is greater than the maximum (max is in $AE$5 and equals the number of people with the most points; in this case, it's 5) and to make a blank cell if it is. Since there are 5 people with 10 points, there should be (and there are) 5 people in the "Winner" list. The points column in the Winner area takes into account a tie-breaker formula (which is working properly). The ranking column is column AA, and the points column is column AC. If the value of AC (rounded to 2 decimal spots) in one row is equal to the value one row higher, the rank# should be the same, but if it isn't, it should be equal to the count from the first row to the current row.

I evaluated the formula all the way to the last step, and below is a picture of that. As you can see, the first IF is false, so it doesn't use the value of the cell above it, and the false return is supposed to be '3.' But, as you can see, the returned value is '1.' I'm at a complete loss on this one. The green fill is for cells with a value of 1, so that's working fine. I just don't understand why it's returning a 1 and not a 3 (for the selected cell).

 

[NdNoviceHlp]

Remove the Round from your formula. HTH. Dave

 

[Fluff]

You have a circular reference in that formula, for the last part try
COUNT($AC$4:$AC6)

 

[CDelSignore]

You have a circular reference in that formula, for the last part try
COUNT($AC$4:$AC6)

Thank you for responding, Fluff. Even with the uncircularizing (lol), it doesn't change the output. I went to COUNT() when the last part was "ROW($AC6)-ROW($AC$4)+1" and was returning the same wrong value.

Remove the Round from your formula. HTH. Dave

Thanks for responding, Dave. To differentiate between values so I don't return the same name with the same point value, the values are different 4 or 5 decimal positions back. That's why I went with the ROUND().

I've used this same strategy for populating a "Top 5" list on other spreadsheets without any issues. I'm perturbed this one isn't working properly.

 

[Fluff]

What is the formula in AA4?

 

[CDelSignore]

What is the formula in AA4?

1

That cell will always be the number 1, so there is no formula at all.

 

[Fluff]

In that case what is the formula in AA5?

 

[CDelSignore]

In that case what is the formula in AA5?

It's the same as the one in AA6, but with 5's instead of 6's (spaces added to formulae for attempts at clarity).

= IF (ROW(AB5) - ROW($AB$4) >= $AE$5 ,"", IF(ROUND(AC5,4) = ROUND(AC4,4), AA4, COUNT($AA$4:$AA5)))​

It's a copy down from AA5 all the way down to AA63.

What's really getting me confuzzled, though, is that when I evaluate the formula, prior to the final evaluation, the formula is:

= IF (FALSE, #NA , 3)​

After the final evaluation, the result is 1, when it should be 3. It should be 3 based on the formula and as evaluated.

 

[Fluff]

The COUNT is still looking at Col AA, therefore you have a circular reference which is why you are getting the results you are.
Try
= IF (ROW(AB5) - ROW($AB$4) >= $AE$5 ,"", IF(ROUND(AC5,4) = ROUND(AC4,4), AA4, COUNT($AC$4:$AC5)))

 

[CDelSignore]

The COUNT is still looking at Col AA, therefore you have a circular reference which is why you are getting the results you are.
Try
= IF (ROW(AB5) - ROW($AB$4) >= $AE$5 ,"", IF(ROUND(AC5,4) = ROUND(AC4,4), AA4, COUNT($AC$4:$AC5)))

It had to look at AA4. If the rounding comparison was true, it was to use the same number as AA4. But, you were correct a circular reference definitely was the issue. I had to change the formula in AC4. That formula is now:

= IF ($AD4="", "", LARGE(ROUND($Z$4:$Z$63,5), (ROW($AC4)-ROW($AC$4)+1)))

It used to look at column AA instead of column AD in the first IF statement, which caused the circular reference. AD4 is always 1, but the formula for AD5 (and filled the column) is now:

= IF (ROW(AD5) - ROW($AD$4) >= $AE$5, "", ROW(AB5) - ROW($AB$4)+1)

This was the original formula starting in AA4 and down, but that will not show ties, so if 5 people all had 10 wins, it will always show 1, 2, 3, 4, 5. The IF statement with the rounded cells would identify ties and deal with them appropriately. After I finally got it through my thick skull and found the root error, the output is now:

Fluff, thank you for your assistance and patience!!