Forum Discussion
Filter no choice between number and text
Above is my example, Column BA shows the daily price changes of the index. Then I used these formulas to separate the up days and the down days into their own columns.
Up days: =IF($BA2>=0,$BA2,"")
Down Days: =IF($BA2<0,$BA2,"")
So column BB gives me a number filter and I can then filter for different values.
Column BC only gives me a Text Filter and no choice to make it a number filter. The only way I can use it to filter different number values is to make more columns getting those values out of the column or making the entire column numeric.
Looking more closely at your image, it appears that we're both working with stock market data. And looking at your two adjacent columns, I have a suggestion:
Why don't you use conditional formatting to differentiate the Up days from the Down? All of the Downs are negative values, the Ups are positive. Excel can automatically show negative numbers in red, positive in black. Put them all into one column which would then be all numeric.
- because I am not only interested in the fact that a day is negative or positive , I need to know the size of the changes. It is only one factor out of hundreds that I look at.
Of course you're interested in size, i.e., magnitude of numbers, not just direction (positive or negative), and that's why what I wrote mentioned Excel will automatically highlight negative NUMBERS in red, positive in black.
I do the same. I also use FILTER and other functions to extract those with largest magnitude (positive or negative) or largest change.....
My main point was to say if you're trying to use the Filter menu tool and running into obstacles because of the numeric vs text matter that you posted about, there are other ways to accomplish your objective. Excel almost always offers users different routes from point A to point B...part of the fun of using it is to explore those alternative routes.