| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula for Conditional Format
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) . |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula for Conditional Format
=$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) "TUNGANA KURMA RAJU" wrote in message ... 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) . |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula for Conditional Format
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" wrote in message : =$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) "TUNGANA KURMA RAJU" wrote in message ... 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) . |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula for Conditional Format
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;FA LSE} and MIN can function okay as it ignores the FALSE values -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "JMay" wrote in message ... 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" wrote in message : =$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) "TUNGANA KURMA RAJU" wrote in message ... 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) . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula for Conditional Format
=$C1=MIN(IF($A$1:$A$10000=$A1,$C$1:$C$10000))
"TUNGANA KURMA RAJU" wrote: 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) . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula for Conditional Format
VERY interesting, this is a very good Rule to learn.
Always appreciate your help Thanks Bob "Bob Phillips" wrote in message : 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;FA LSE} and MIN can function okay as it ignores the FALSE values -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "JMay" wrote in message ... 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" wrote in message : =$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) "TUNGANA KURMA RAJU" wrote in message ... 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) . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula for Conditional Format
Also using the MIN -- it must be CSE'd (Ctl+Sft+Enter)
This makes the distinction from MAX even more interesting. Jim "Bob Phillips" wrote in message : 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;FA LSE} and MIN can function okay as it ignores the FALSE values -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "JMay" wrote in message ... 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" wrote in message : =$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) "TUNGANA KURMA RAJU" wrote in message ... 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) . |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula for Conditional Format
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) "JMay" wrote in message ... Also using the MIN -- it must be CSE'd (Ctl+Sft+Enter) This makes the distinction from MAX even more interesting. Jim "Bob Phillips" wrote in message : 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;FA LSE} and MIN can function okay as it ignores the FALSE values -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "JMay" wrote in message ... 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" wrote in message : =$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) "TUNGANA KURMA RAJU" wrote in message ... 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) . |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula for Conditional Format
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" wrote: =$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) "TUNGANA KURMA RAJU" wrote in message ... 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) . |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula for Conditional Format
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) "TUNGANA KURMA RAJU" wrote in message ... 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" wrote: =$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) "TUNGANA KURMA RAJU" wrote in message ... 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) . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find value in array | Excel Worksheet Functions | |||
| Date format changes to formula | Excel Discussion (Misc queries) | |||
| Format Issue | Excel Worksheet Functions | |||
| keeping cell format with formula | Excel Worksheet Functions | |||
| Convert Excel 4 formula to Excel 2003 format | Excel Worksheet Functions | |||
Linear Mode
