Forum Discussion
Filter no choice between number and text
I can't answer the question why it doesn't give you a choice, because when I went and looked for an example in one of my own spreadsheets, it DID give me a choice. The formula that I have for that column has exactly the same logic as yours, returning either "" or a number. And this image shows what I get in the Dialog box if I ask to filter.
So could you give a more detailed picture (literally or figuratively) of what happens in your situation?
skylanetk You wrote:
It is my understanding the if more numbers show up in the column then ,"", text responses EXCEL automatically makes the number filter show. The problem comes if the "" text response is the larger then EXCEL automatically chooses the text filter and you don't have a choice. Then you must make your answer in all of the cells in the column numeric. That creates other problems when you say average the numbers in the column.
But my column--the one from which my image of the filter dialog box was taken--has more cells with the "" value than with a numeric value. Yet it's still showing as it is pictured. It's possible we're working with different versions of Excel, I suppose. Mine is the most recent version.
- I am a Microsoft office user also, Microsoft 365.
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.