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

#### sngbrdb

##### New Member
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??

### 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
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?

Replies
6
Views
89
Replies
5
Views
97
Replies
1
Views
113
Replies
8
Views
80
Replies
2
Views
62