Number column filter changes to text if formula entered in row 15717!?

sngbrdb

New Member
Joined
Mar 22, 2019
Messages
1
Have a column that is formatted as numbers (with comma, no decimal). In it is this formula:

Code:
=IF(AND($J2=$J3,$L2=$L3),$N2-$N3,"")

For the first 15,716 rows, the filter on that column is a number filter - regardless of which expression the if evaluates to. When I copy the formula to the 15,717th row, the filter changes to text. If I change the value in the 15,717th row to a number like "3", the filter flips back to a number filter. If I clear the field (no value), it's a number filter.

If I copy the formula text from a higher cell - so that 15,717 refers to cells that result in a number filter higher up - I get a text filter.

This formula in G15715: Filter is a number filter
Code:
=IF(AND($J15715=$J15716,$L15715=$L15716),$N15715-$N15716,"")

The same formula text in G15717: Filter is a text filter
Code:
=IF(AND($J15715=$J15716,$L15715=$L15716),$N15715-$N15716,"")

If I change the formula in 15717 to be zero instead of "" , or change the formula to take the true value, I get a number filter.
Code:
=IF(AND($J15717=$J15718,$L15717=$L15718),$N15717-$N15718,0)
=IF(AND($J15717=$J15718,$L15717<>$L15718),$N15717-$N15718,"")

If I enter the formula into 15,717 and have a text filter, deleting a higher row drops 15,717 to row 15,716 - and voila! I magically have a number filter again. If I copy the formula to the new row 15,717... text filter. So it is absolutely not the data in any of the cells - it is the fact that it is the 15,717th row.

Why in the blessed name of god-I-hate-microsoft would the filter behavior at row 15,717 change??
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

sunrise06

Active Member
Joined
Oct 27, 2006
Messages
254
This issue intrigues me. Can you humor me and tell me the values you have in columns J, L, and N for rows 15715 and 15716?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,550
Messages
5,529,472
Members
409,884
Latest member
Msinmath
Top