Exclude Rows

amerifax

Board Regular
Joined
Dec 17, 2008
Messages
247
I am looking to run a formula against my data, but I am not sure how to do it or where to start.

Col J is a Total Count for 2009. Col M is a Total Count for 2010 YTD. I only want to see the records where Col J is greater than 4 and Col M is less than 50% of Col J.

Any help would be greatly appreciated.

Heather
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
in row 2 of a helper column:
=AND(J2>4,M2<(J2/2))
and copy down. Shows TRUE for the rows you want to see.
Autofilter the whole table including the helper column for TRUE in that column.
 
Upvote 0
That sounds easy enough, I will give that a try.

Thanks so much for your help, I really appreciate it!

Heather
 
Upvote 0
Just one more quick question for you...Is there a way to change for Color for the True results within the formula to red? Also can I use something besides True, False to display the results?

If not, thank you again, your formula has still been a great help.

Heather
 
Upvote 0
Instead of
=AND(J2>4,M2<(J2/2))
have
=IF(AND(J2>4,M2<(J2/2)),"Slackers!","Well done!")
(untested). You don't have to show anything; just use "" instead of one of the strings.

In terms of colour, yes, use conditional formatting. Either let it look at the value in the helper column, or use the original formula (tweaked a bit) in the Formula Is part of conditional formatting:
=AND($J2>4,$M2<($J2/2))
and make sure you start by entering the conditional format when a cell on row 2 is the active cell, then you'll be able to use the Format Painter to copy that conditional formatting to other cells.
 
Upvote 0

Forum statistics

Threads
1,215,776
Messages
6,126,830
Members
449,343
Latest member
DEWS2031

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
Back
Top