Formula for Conditional Formatting Bottom 5 excluding 0's

**FlyinIron406** · 05-08-2013, 09:36 PM

Hey everyone!

I'm trying to use Conditional Formatting on a group of cells that include balances, and some that have just letters/place fillers (XXXX) in them.

I can't get the "Bottom 5" to work because it uses the 0's. I've tried a few formula's and have been unsuccessful.

Here is my sad attempt:

=SMALL(IF(E$3:E$26>0,E$3:E$26)E$3:E$26,5)

I know something isn't right, and I can't quite figure out how to make it work. If I only use this below...

=MIN(IF(E$3:E$26>0,E$3:E$26))

....I can get it do format the minimum value that is not 0 of all the cells. I sadly cannot figure how to combine them. I like the Bottom 5, but I also wonder if there is a way to use the Data Bar with formula in this instance?

Thanks!

**vlady** · 05-08-2013, 10:10 PM

maybe like this

=AND($E3<SMALL(IF(E$3:E$26>0,E$3:E$26),6),$E3>0)

**FlyinIron406** · 05-08-2013, 10:19 PM

Hey Vlady,

Thanks for the reply. I have put your formula in the Conditional Formatting > Classic > Use a formula to format > box. It didn't seem to work. It only formatted 2 cells, one of which was the 3rd from the bottom and a cell with a zero in it. Is having the XXXXX place fillers creating a problem you think?

Thanks!

**vlady** · 05-08-2013, 10:30 PM

maybe you should provide a sample on the workbook you are working.

To Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**FlyinIron406** · 05-08-2013, 10:53 PM

Hey,

Thanks. I have attached the following, that gives an idea. For some reason, the code you provided works on this sample data! Yet, not on my actual log. On my actual workbook, all of the numbers are pulled from a separate sheet, and the formatting is >0 red, <= green, with US currency. I am not sure that the data being pulled from another spot matters or not?

Thanks!

**vlady** · 05-08-2013, 11:05 PM

how did you pull the data. what did you use?

**FlyinIron406** · 05-09-2013, 12:18 AM

Thanks for your patience and help!

The data in this column is pulled from an entry sheet in the same workbook. Just another worksheet. I pull the data using the [ = ] sign and then browsing to that sheet, selecting the cell, and hitting enter.

**FlyinIron406** · 05-09-2013, 12:50 AM

Okay,

I may have narrowed it down to the Conditional Formatting of the numbers based on value. In this new sample, you can see how I have my actual doc formatted. >.01 is red, < .001 or whatever is green. I also have the formula format you kindly provided too.

So, based on your code, without the Conditional for Red and Green, it works great. The second I add that formatting, it does not work again.

Thanks for any info and insight you can provide.

**vlady** · 05-10-2013, 01:36 AM

look attachment.

Copy of sample2.xlsx

**FlyinIron406** · 05-10-2013, 02:23 AM

Wow vlady,

This seems to work great! I hope it wasn't too much effort, as I am completely lost when I go through the code!

A few things to add, or I'm curious about:
1. If only one cell has a positive number, it will be black and not formatted red. I am unsure what value to change! If it is 0 or negative it formats correctly.
2. Now, this isn't a biggie by any means. Would it be possible to have it use the custom format fill it does on the 5 lowest, apply to the next 3 cells adjacent?
Ex: If cell E15 is one of the 5 lowest, and is filled, F15 (amt due), G15 (extra), and H15 (total paid) would also fill?

Thank you SO MUCH for your help! Amazing

Formula for Conditional Formatting Bottom 5 excluding 0's

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Formula for Conditional Formatting Bottom 5 excluding 0's

Re: Formula for Conditional Formatting Bottom 5 excluding 0's

Re: Formula for Conditional Formatting Bottom 5 excluding 0's

Re: Formula for Conditional Formatting Bottom 5 excluding 0's

Re: Formula for Conditional Formatting Bottom 5 excluding 0's

Re: Formula for Conditional Formatting Bottom 5 excluding 0's

Re: Formula for Conditional Formatting Bottom 5 excluding 0's

Re: Formula for Conditional Formatting Bottom 5 excluding 0's

Re: Formula for Conditional Formatting Bottom 5 excluding 0's

Re: Formula for Conditional Formatting Bottom 5 excluding 0's

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions