Highlight lowest 3 values in an array

noahsmom

New Member
Joined
Nov 7, 2005
Messages
9
:banghead: Tried the following & several other ways:

Using conditional formatting - Cell Value is Equal To...

Condition 1
=MIN(H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF7,EN7)
Highlight yellow
Condition 2
=SMALL((H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF7,EN7),2)
Highlight green
Condition 3
=SMALL((H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF7,EN7),3)
Highlight blue

gives me the following error:
You may not use unions, intersection, or array constants for conditional formatting criteria.


Based upon a spreadsheet that looks like this...
Sample.xls
ABCDEFGHIJKLMN
1(IdentifyingVendor123
2Information)ContactJane
3Quote #1A2B
4Valid12/31
5
6Item#ItemDescQtyUOMUnitCostExtCostBidExtBidTermsDeliveryFreightFOBStdPkgMinBuy
7226801ABC2ea1.553.101.603.207Add55
8226802DEF11ea1.5116.611.5717.277Add55
9226803HIJ2ea1.563.121.613.227Add55
10226807KLM4ea2.369.442.409.607Add55
11226808NOP6ea0.281.680.321.927Add55
12226809QRS2ea1.693.381.733.467Add55
13226810TUV1ea2.042.04
14244500WXYZ5ea10.2351.1510.2751.357Add55
Sample


columns G thru N may be repeated numerous times (different vendors). I need the lowest 3 values under the EXT COST column excluding the blank cells. The array will always remain the same (H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF,EN).

Since conditional formatting doesn't seem to be doing the trick, does anyone have any additional ideas? Possibly a macro?

I hope that I have provided enough information. ANY help would be more than greatly appreciated!!!!

Thanks,
Noahsmom :pray:
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
An example

Try this for condition 2 ( and adjust for condition 3 ), instead of Cell Value Is:
Code:
=SMALL((H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF7,EN7),2)
do Formula Is, with this formula:
Code:
=H7=SMALL(IF(MOD(COLUMN($H$7:$EN$7),8)=0,$H$7:$EN$7,9999),2)
 
Upvote 0
Thanks a million for the example, but I can't seem to make it work. It's highlighting the blank cells yellow and then several others green, and nothing blue.

I even made several futile attempts in modifying the code. Another suggestion maybe?
 
Upvote 0
Select only cells H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF7,EN7 ( click H7, hold the ctrl key, then click each of the other cells ), before doing the Conditional Formatting.

The example I gave was for Condition 2. You can use =B7=MIN(etc for Condition 1. Use my formula with a ,3 instead of ,2 for Condition 3.
 
Upvote 0
Thanks again, Glenn. But....I selected the referenced cells using the contol key, chose conditional formatting with:

Condition 1
Cell Value is Equal to
=MIN(H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF7,EN7)
Condition 2
Formula is
=H7=SMALL(IF(MOD(COLUMN($H$7:$EN$7),8)=0,$H$7:$EN$7,9999),2)
Condition 3
Formula is
=H7=SMALL(IF(MOD(COLUMN($H$7:$EN$7),8)=0,$H$7:$EN$7,9999),3)

and I'm at a loss. It still doesn't work for me. It should, I KNOW it should, but it ISN'T. :banghead: I'm so frustrated.
 
Upvote 0
Condition 1 should have been:
Code:
=H7=MIN(H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF7,EN7)

Can you fix that one, and see if it all starts working?
 
Upvote 0
Changing Condition 1 to =H7=MIN... highlights the BLANK cells yellow.

Condition 1 will work correctly by itself without the =H7 and with adding your Condition 2, but then the Condition 2 doesn't work correctly. And then Condition 3 isn't working either.

I'm beginning to feel like a complete moron. I know that it is something so extremely simple...

Again, I sincerely appreciate your trying to help with this.
 
Upvote 0
How can it be highlighting the blank cells? You should have selected the cells H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF7,EN7 for Conditional Formatting ... which doesn't include the blank cells ( or does it???? ).

Anyway, do any of your cells get highlighted Green?
 
Upvote 0
Yes, some of these cells (H7,P7,X7,AF7,AN7,AV7,BD7,BL7,BT7,CB7,CJ7,CR7,CZ7,DH7,DP7,DX7,EF7,EN7) CAN be blank.

And Yes, several of the selected cells get highlighted green (Cond 2) but none get highlighted blue (Cond 3).
 
Upvote 0
Ah, it's the blank cells that will be causing the problems. I have to go now. I'll look at this tomorrow and try to design some condition formulae then.
 
Upvote 0

Forum statistics

Threads
1,224,391
Messages
6,178,306
Members
452,839
Latest member
grdras

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