Filter Feature has Text Filters, but I want number filter

Hey all,

I am trying to design a database spreadsheet for work, I am a real estate appraiser and we are constantly juggling tons of sales comp data. So, I've put together a spreadsheet that I thought I had completely ironed out, and professional looking. But when I went to filter the sales, certain columns are not displaying number filters, but text filters instead. I've attached the spreadsheet for reference.

After some searching with some people with similar problems, it appears that the =IF function is causing the problem, and that I have inadvertently told Excel to spit out text displayed as numbers, when it should just be numbers.

The reason for using the =IF Function was so that the spreadsheet wouldn't spit out "#DIV/0!", or "#VALUE!" and look professional.

Take column K for example ($/Unit) - right now I have the formula entered "=IF(E3=" "," ",J3/E3)"

With this entered, the number filter works, and I can search apartment comps by sale price per unit. But, then I have the annoying "#VALUE!" displayed in all columns underneath where future comps will be entered.

If I change the formula to read "=IF(E3=0," ",J3/E3)", the "#VALUE!" disappears, but then the number filter doesn't work, and excel forces Text Filtering.

The number filter is the important part, as this database grows, there will literally be hundreds of apartments to filter through. I need to be able to see results specified within a unit price range.

The tough part is that several other pieces of data that affect the $/unit are subject to similar =IF formulas, so untying the knot to get it to work may be challenging.

Any thoughts? Thanks in advance.

Have you tried changing "=IF(E3=" "," ",J3/E3)" to "=IF(E3="","",J3/E3)"

The difference is that the formula is looking for an empty cell rather than one containing a space.

Hope this helps

Windy

Windy,

Thanks for the response, but that does not work. It worked when only applied to the three datasets entered, but as soon as I applied the formulas to where future comps were to be entered, it reverted back to text filtering.

The issue appears to be that cells slotted for future data entry are confusing the filter.

Try

=IF(E3="",0,J3/E3)

Then go to File - Options - Advanced
"Display Options For this WorkSheet
UNcheck "Show a zero in cells that have zero value"

Jonmo1,

Okay, that works, but then there is a $0 displayed in all future columns. Is there a way to hide this number? That is almost as annoying as displaying "#DIV/0!"

You're welcome.

Jonmo1,

SORRY! Just saw the rest of your response, I'll give "show a zero in cells..." a try.

Jonmo1,

Okay, that works. THANK YOU.

Its a less than perfect solution, however, as there are other columns in the spreadsheet where a zero is often entered, and in some cases I would like the zero to be there, otherwise it may confuse other users.

Is there a way to apply it only to certain columns? Any other work arounds?

In all of your formulas you have the following " " this implies that there is something in the cell when in fact they are blank.

If you change " " to "" does this solve the issue.

Don't forget to copy down through all the rows in the sheet.

Windy

(Which columns are you filtering by?)

Windy,

I tried changing the " " to "", it doesn't work. I've removed all the formulas from line 6 and below, and doesn't work. Or at least I can't figure it out.

I am trying to filter by several columns - could be different depending on what i am looking for. Could be by per unit and City, or by County and unit, or by unit, county and gross sales price. We are constantly digging for proverbial needles in the haystack.

In the end, I can just leave the "#DIV/0!" and other undesirable outputs, but just trying to get it just perfect. If I can't do it, then I can't do it.

You can apply a custom format on specific columns..

$#,##0_);($#,##0);

Windy,

I also checked other columns that have the " " syntax, and the number filter still works there. It appears that since column K ($/Unit) is dependent on column J (****. Sale Price) may be causing the issue.

Column J is calculated by G + H + I. Maybe that is causing the issue?

Another option you might consider is to utilize the 'Tables' Feature of XL.
If you convert the data to an Excel Table, you will no longer have the need to extend formulas down the column to accomodate future data.
Those formulas will auto-extend as new data is added to the table.

The only column that returns a #VALUE/#DIV/0! errors is column K, as I said before this is due to part of the formula looking for a non empty cell in column E.
Column E is either a number or is blank so change the E3=" " part to E3="" in column K.
This removes both errors from the filter option.

Windy,

Okay, I tried that, but when you do that it removes the #Value/#Div/0!" output, but then you cannot filter column K with a number filter, and only a text filter. A text filter will not work for my purposes.

Unless I am missing something? Just to be sure I applied the changes to the same spreadsheet that I uploaded.

After applying your suggested changes, and clicking on the filter key, the "number filter" option is missing, and the "text filter" is in it's place.

That is the problem. I can make the improper output disappear from future columns, but than I can't apply number filters.

Are we on the same page? Or am I missing something?

Windy,

Now I am really confused. So, I tried your advice, again, and went through every column that had a formula in it, and changed any " " to "", I made sure to remove all formulas in future columns, anything below line 5, and then made sure to apply the changes by dragging down a few lines.

At this point, all filters were working correctly.

until I tried entering a new comp in line 6, as soon as I got done filling in all the columns, the text filters came back, and the number filters stopped.

The problem seems to be arising when the formulas recalculate any equation.

Completely stumped at this point, may have to move to plan B.

It's because the formula is returning a mixture of numbers and text.
If the values ARE there, then the formula returns a number.
If the values are NOT there, then the formula returns "" <- a text string

Originally Posted by 6string

Windy,

Now I am really confused. So, I tried your advice, again, and went through every column that had a formula in it, and changed any " " to "", I made sure to remove all formulas in future columns, anything below line 5, and then made sure to apply the changes by dragging down a few lines.

At this point, all filters were working correctly.

Because at that point, none of the formulas are actually returning "", they are all returning actual numbers.
You have not yet extended the formulas down to blank rows beyond your actual used area.
Therefor the column contains only numbers.

Originally Posted by 6string

until I tried entering a new comp in line 6, as soon as I got done filling in all the columns, the text filters came back, and the number filters stopped.

Now at this point, you have extended the formulas beyond your actual used area.
Therefor the formulas below the last actual used row are returning "" text strings.
So the column now contains a mixture of text and numbers.


The only solution I can see is to make your formulas return 0 instead of ""
Then hide the zeros as I suggested earlier via the Options or a custom format.

Jonmo1 - Thanks for taking the time to look at this, seriously, much appreciated. Thanks to Windy as well.

Looks like it's three options:

1. Abandon the =IF function and leave the undesired output in future columns.

2. Replace "" with 0 and hide the zeros

3. Use a custom text filter which allows for a similar "greater than less than" search.

Thanks to all that took a look at this for me. Greatly greatly appreciated.

Glad to help, this is actually an interesting thing that is going on.

After a little toying around with a column containing a mixture of numbers and text
It appears Excel picks the Majority and uses that to determine if it shows Text or Number filters.
If there are more numbers than text, then it shows the Number filter.
And Vice Versa.

So I would think once you actually apply it to your real data set, you'll actually have the number filters instead of text.
I would presume you would have more actual data, than blank rows.

I would suggest extending the formulas only about 10% further down than your data actually occupies.

Theoretically, the spreadsheet could hold hundreds, if not thousands of lines of data, within a pretty short time frame. Obviously, we don't use old datasets, probably cut it off at the last three years, and anything older than three years placed in an archive.

The real problem is that I am trying to make this thing as user friendly as I can so that people with less computer skills can contribute. But I don't want those people messing up my formulas, and the data needs to be standardized so it can be found/filtered by others. Plus, it has to be easy enough so that I can convince those less technically inclined to buy in and adopt.

I think I found a workable solution. Those not interested in custom filters can just spend a lot of time clicking little boxes in the filter section

Take it easy.

I'll suggest Tables Again.
As user friendly as it gets.

Formula in 1 column will auto incriment as you add new data to another column.

Does a table have the same functionality as a spreadsheet? I don't know if you noticed, but I've also put it a bunch of drop down menus, there is data validation to force users, etc. Looks like it works.

another quick question, does the cutting and pasting work the same way? The goal is that once the user has filtered down to the dataset they have, you can copy it out of there, and paste to another spreadsheet. The formatting/formulas are not important in the pasted copy, btw.

Give it a try and see.

The Table has all the same funcitonality that existed before (plus more functionality)

Only one way to find out if it will work for you...

Once again, thanks. We can consider this thread "solved".

You're welcome.