IF statement returning incorrect value

CDelSignore

New Member
Joined
Mar 1, 2016
Messages
18
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.

CWGPOYB.jpg


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).

dBYfGqw.jpg


CWGPOYB
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
You have a circular reference in that formula, for the last part try
COUNT($AC$4:$AC6)
 
Upvote 0
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.
 
Last edited:
Upvote 0
In that case what is the formula in AA5?
 
Upvote 0
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.
 
Upvote 0
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)))
 
Upvote 0
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:

chSNkPJ.jpg



Fluff, thank you for your assistance and patience!!
 
Upvote 0

Forum statistics

Threads
1,224,585
Messages
6,179,697
Members
452,938
Latest member
babeneker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top