Advanced Auto Filter

[NYEXCEL1]

I have the table below, when I select my auto filter criteria (Overservice <50% IN RED) all rows except the first are hidden rather than just the ones over 50%. Can anyone see why this may be occuring. thanks

Overservice	Optimal Time Cost	Time Cost Reduction	Avg Rate	Hours		Overservice
151.5%	$ 27,455.79	$ 18,582.96	$ 147.50	126		<50%
61.9%	$ 28,731.51	$ 2,274.74	$ 129.64	18
25.0%
46.5%			$ 170.20
81.9%	$ 28,500.00	$ 6,068.75	$ 175.24	35
185.9%	$ 29,462.60	$ 26,693.66	$ 165.26	162
133.7%	$ 28,613.16	$ 15,974.34	$ 164.03	97

<tbody>
</tbody><colgroup><col><col><col><col><col><col span="2"></colgroup>

 

[Andrew Poulsom]

Are you using Advanced Filter or AutoFilter? For me Advanced Filter hides all except rows 4 and 5.

 

[NYEXCEL1]

Advanced Filter. With the filter being <50% all rows should show except 3 & 4

 

[Andrew Poulsom]

Are you sure that the entries in the Overservice column are actually numbers? You can use ISNUMBER to check. Here's what I get:

Excel 2010
	A	B	C	D	E
1	Overservice	Optimal Time Cost	Time Cost Reduction	Avg Rate	Hours
4	25.00%
5	46.50%			$ 170.20
Sheet2

 

[NYEXCEL1]

They are as ISNUMBER shows "TRUE" I do have an IFERROR formula where the error will return a "-" I dont know how to return a numeric value that this filter formula will recognize as "0" returns the ISNUMBER formula as "FALSE"

How are you copying your spreadsheet into the dialogue box?

IN the advanced filer box I am selecting all cells under the "Overservice" Column and my filter criteria is "Overservice' & "<50%"

Why i apply all rows are hidden but the first row. I dont understand.

 

[Andrew Poulsom]

You need to select the headings too. I filtered A1:E5 with the criteria G1:G2.

 

[NYEXCEL1]

I got it to work but i put the criteria filter above the set of data. is that the only way it works?
Also is there a way I can use a formula to link this criteria to another user inputted cell? For example, i have a number of forumulas driven by the percentage entered, in this case 50%. can I enter a formula in the cell the autofilter picks up that will pickup the user entered infomation? ie. (>&"K1")

 

[NYEXCEL1]

Sorry one other question.. if i insert a chart will it show the data that is filtered or all data within the data series. in other words will the chart change based upon the data that is shown

 

[Andrew Poulsom]

For the criteria try:

="<"&K1

A chart will plot only the visible cells provided you have checked that option.

 

[NYEXCEL1]

ok thank you.

Regarding my chart, i am plotting a client, the period and overservice percentage. in my data the client can repeat multiple times as we are charting overservice by different periods (Jan, Feb, etc) Is there a way to do a dropdown menu on a chart where i can choose the one client and then it will plot the periods on the horizontal axis and the percentage on the vertical?