MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Identifing prices in columns


Posted by Al Polan on October 10, 2001 8:22 AM

I have some 3,000 rows of parts and 5 Columns of different vendors prices in Columns B thru F, how would I be able to make a distinction in the 5 Columns of prices of the lowest next lowest and so on either in color font style or some other method of identification in each row?

Thanks in advance for any and all help.


Posted by Aladin Akyurek on October 10, 2001 8:49 AM

Al,

You can use conditional formatting for this task.

Select all rows from B to F.
Activate Format|Conditional Formatting.
Select "Formula Is" for Condition 1 in the Conditional Formatting window and enter the following formula:

=B1=SMALL($B1:$F1,1)

Activate Format and choose a color for the lowest price via the Patterns tab.

Don't leave the window.
Activate Add. Enter the following formula for the next lowest price:

=B1=SMALL($B1:$E1,2)

Select the color for the next lowest price via Format.

Aladin

==========

Posted by Al Polan on October 10, 2001 12:45 PM


The first two work fine but I can not get the third one to work using =B1=SMALL($B1:$D1,3) Am I doing something wrong or will it not do three?

Posted by Aladin Akyurek on October 10, 2001 1:23 PM

> The first two work fine but I can not get the third one to work using =B1=SMALL($B1:$D1,3) Am I doing something wrong or will it not do three?

Three conditions constitute indeed the maximum.

If you want more than 3 colors, you'll need VBA.

That you didn't get the 3rd condition to work is strange.

Select your whole range and try to define 3 conditions all at once.

Aladin