formula for Conditional Format

[Guest]

I have range A2:C10000 as
item--------------Supplier----------Price
col A---------------Col B------------Col C
pen ----------------Abb ------------1.90
pen ----------------Cad ------------2.00
pen ----------------Ham -----------1.95
Book---------------Cad -------------3.20
Book----------------Ham -----------4.50
Book ---------------Abb -----------3.50
Book ---------------Jal ------------3.10
Ink ----------------Abb------------21.50
Ink -----------------Bom-----------21.00
What formula is to be used in Conditional format to get lowest bid price of
each item and the supplier(Col B & Col C) .

 

[Bob Phillips]

=$C1=MAX(INDEX(($A$1:$A$1000=$A1)*$C$1:$C$1000,0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[JMay]

Bob -- I tried substituting MIN (for low bid price) for MAX
But get 0's in E2:E10; What's going on?

Range Below (in my example) A1:C10 << my formulas are in Column E2:E10
Item Supplier Price
pen Abb 1.90
pen Cad 2.00
pen Ham 1.95
Book Cad 3.20
Book Ham 4.50
Book Abb 3.50
Book Jal 3.10
Ink Abb 21.50
Ink Bom 21.00

 

[Bob Phillips]

Jim,

The problem is that this part of the formula

INDEX(($A$1:$A$10=$A1)*$C$1:$C$10.0)

will return the following array when looking at Pen

{1.9;2;1.95;0;0;0;0;0;0;0}

that is, all the values associated with Pen, zeroes when not Pen.

Clearly, taking the MIN of this will give zero.

To get the MIN, you could use

MIN(IF($A$1:$A$10=$A1,$C$1:$C$10))

in this case the

IF($A$1:$A$10=$A1,$C$1:$C$10)

check returns

{1.9;2;1.95;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

and MIN can function okay as it ignores the FALSE values


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

=$C1=MIN(IF($A$1:$A$10000=$A1,$C$1:$C$10000))

 

[JMay]

VERY interesting, this is a very good Rule to learn.
Always appreciate your help
Thanks Bob

 

[JMay]

Also using the MIN -- it must be CSE'd (Ctl+Sft+Enter)
This makes the distinction from MAX even more interesting.
Jim

 

[Bob Phillips]

Yes, that is right. Sorry I forgot to mention that bit (also why I used the
INDEX formula for MAX).

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Bob,This gave me highest bid price,I want lowest bid price.I changed the
formula with MIN but it is not giving desired results.

 

[Bob Phillips]

If you want the Min, try

=$C1=MIN(IF($A$1:$A$10=$A1,$C$1:$C$10))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)