Advanced Auto Filter

NYEXCEL1

Board Regular
Joined
Apr 17, 2013
Messages
121
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

OverserviceOptimal Time CostTime Cost ReductionAvg Rate HoursOverservice
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>
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Are you using Advanced Filter or AutoFilter? For me Advanced Filter hides all except rows 4 and 5.
 

NYEXCEL1

Board Regular
Joined
Apr 17, 2013
Messages
121
Advanced Filter. With the filter being <50% all rows should show except 3 & 4
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Are you sure that the entries in the Overservice column are actually numbers? You can use ISNUMBER to check. Here's what I get:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;color: #574123;;">Overservice</td><td style="font-weight: bold;color: #574123;;">Optimal Time Cost</td><td style="font-weight: bold;color: #574123;;">Time Cost Reduction</td><td style="font-weight: bold;color: #574123;;">Avg Rate</td><td style="font-weight: bold;color: #574123;;">Hours</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;color: #574123;;">25.00%</td><td style="text-align: right;color: #574123;;"></td><td style="text-align: right;color: #574123;;"></td><td style="text-align: right;color: #574123;;"></td><td style="text-align: right;color: #574123;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;color: #574123;;">46.50%</td><td style="text-align: right;color: #574123;;"></td><td style="text-align: right;color: #574123;;"></td><td style="color: #574123;;">$ 170.20</td><td style="text-align: right;color: #574123;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br />
 

NYEXCEL1

Board Regular
Joined
Apr 17, 2013
Messages
121

ADVERTISEMENT

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.
 

NYEXCEL1

Board Regular
Joined
Apr 17, 2013
Messages
121

ADVERTISEMENT

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

Board Regular
Joined
Apr 17, 2013
Messages
121
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
For the criteria try:

="<"&K1

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

NYEXCEL1

Board Regular
Joined
Apr 17, 2013
Messages
121
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,463
Messages
5,596,284
Members
414,051
Latest member
tabecker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top