| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Question
I have created a pricing matrix in Excel 2003 and I am trying to use the
conditional formatting function to highlight the top 3 cheapest prices in each row. I have tried to set up the formatting, but I continue to get an error message that says "Change the reference to a single cell, or use the reference with a worksheet function." I'm a bit of a novice when it comes to this program so any ideas would be appreciated. Thanks, |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting Question
highlight the top 3 cheapest prices
In other words, you want to highlight the 3 *lowest* prices? Ok, will there *always* be at least 3 prices? Where are these prices? -- Biff Microsoft Excel MVP "lindsayr" wrote in message ... I have created a pricing matrix in Excel 2003 and I am trying to use the conditional formatting function to highlight the top 3 cheapest prices in each row. I have tried to set up the formatting, but I continue to get an error message that says "Change the reference to a single cell, or use the reference with a worksheet function." I'm a bit of a novice when it comes to this program so any ideas would be appreciated. Thanks, |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting Question
Yes, I want to highlight the 3 "lowest" prices, which will change daily. I
have them linked to another page which will be updated everyday. "T. Valko" wrote: highlight the top 3 cheapest prices In other words, you want to highlight the 3 *lowest* prices? Ok, will there *always* be at least 3 prices? Where are these prices? -- Biff Microsoft Excel MVP "lindsayr" wrote in message ... I have created a pricing matrix in Excel 2003 and I am trying to use the conditional formatting function to highlight the top 3 cheapest prices in each row. I have tried to set up the formatting, but I continue to get an error message that says "Change the reference to a single cell, or use the reference with a worksheet function." I'm a bit of a novice when it comes to this program so any ideas would be appreciated. Thanks, |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting Question
Assuming there will *always* be at least 3 prices...
The prices are in the range A1:E1... Select the range A1:E1 Goto the menu FormatConditional Formatting Formula Is: =AND(A1<"",A1<=SMALL($A1:$E1,3)) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "lindsayr" wrote in message ... Yes, I want to highlight the 3 "lowest" prices, which will change daily. I have them linked to another page which will be updated everyday. "T. Valko" wrote: highlight the top 3 cheapest prices In other words, you want to highlight the 3 *lowest* prices? Ok, will there *always* be at least 3 prices? Where are these prices? -- Biff Microsoft Excel MVP "lindsayr" wrote in message ... I have created a pricing matrix in Excel 2003 and I am trying to use the conditional formatting function to highlight the top 3 cheapest prices in each row. I have tried to set up the formatting, but I continue to get an error message that says "Change the reference to a single cell, or use the reference with a worksheet function." I'm a bit of a novice when it comes to this program so any ideas would be appreciated. Thanks, |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting Question
When I enter in the formula - no change occurs.....
Some rows maybe 20 prices, and some may have 10. What I ultimatley am looking to do, is rank the top 3 prices from cheapest, to 2nd cheapest, to 3rd cheapest and have each price highlighted a different color. Sorry if this is confusing.... :) I appreciate your help! "T. Valko" wrote: Assuming there will *always* be at least 3 prices... The prices are in the range A1:E1... Select the range A1:E1 Goto the menu FormatConditional Formatting Formula Is: =AND(A1<"",A1<=SMALL($A1:$E1,3)) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "lindsayr" wrote in message ... Yes, I want to highlight the 3 "lowest" prices, which will change daily. I have them linked to another page which will be updated everyday. "T. Valko" wrote: highlight the top 3 cheapest prices In other words, you want to highlight the 3 *lowest* prices? Ok, will there *always* be at least 3 prices? Where are these prices? -- Biff Microsoft Excel MVP "lindsayr" wrote in message ... I have created a pricing matrix in Excel 2003 and I am trying to use the conditional formatting function to highlight the top 3 cheapest prices in each row. I have tried to set up the formatting, but I continue to get an error message that says "Change the reference to a single cell, or use the reference with a worksheet function." I'm a bit of a novice when it comes to this program so any ideas would be appreciated. Thanks, |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting Question
Ok...
Let's assume the range of interest is A1:G5. Not all rows will have the same amount of numbers in them but the last column that might have a number is column G. Select the range A1:G5 Goto the menu formatConditional Formatting Condition 1 Formula Is: =AND(A1<"",A1=MIN($A1:$G1)) Click the Format button Select the desired style(s) OK Click the Add button Condition 2 Formula Is: =A1=SMALL($A1:$G1,2) Click the Format button Select the desired style(s) OK Click the Add button Condition 3 Formula Is: =A1=SMALL($A1:$G1,3) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "lindsayr" wrote in message ... When I enter in the formula - no change occurs..... Some rows maybe 20 prices, and some may have 10. What I ultimatley am looking to do, is rank the top 3 prices from cheapest, to 2nd cheapest, to 3rd cheapest and have each price highlighted a different color. Sorry if this is confusing.... :) I appreciate your help! "T. Valko" wrote: Assuming there will *always* be at least 3 prices... The prices are in the range A1:E1... Select the range A1:E1 Goto the menu FormatConditional Formatting Formula Is: =AND(A1<"",A1<=SMALL($A1:$E1,3)) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "lindsayr" wrote in message ... Yes, I want to highlight the 3 "lowest" prices, which will change daily. I have them linked to another page which will be updated everyday. "T. Valko" wrote: highlight the top 3 cheapest prices In other words, you want to highlight the 3 *lowest* prices? Ok, will there *always* be at least 3 prices? Where are these prices? -- Biff Microsoft Excel MVP "lindsayr" wrote in message ... I have created a pricing matrix in Excel 2003 and I am trying to use the conditional formatting function to highlight the top 3 cheapest prices in each row. I have tried to set up the formatting, but I continue to get an error message that says "Change the reference to a single cell, or use the reference with a worksheet function." I'm a bit of a novice when it comes to this program so any ideas would be appreciated. Thanks, |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting Question
It worked!!! Thank you SO much for your help!!
Lindsay "T. Valko" wrote: Ok... Let's assume the range of interest is A1:G5. Not all rows will have the same amount of numbers in them but the last column that might have a number is column G. Select the range A1:G5 Goto the menu formatConditional Formatting Condition 1 Formula Is: =AND(A1<"",A1=MIN($A1:$G1)) Click the Format button Select the desired style(s) OK Click the Add button Condition 2 Formula Is: =A1=SMALL($A1:$G1,2) Click the Format button Select the desired style(s) OK Click the Add button Condition 3 Formula Is: =A1=SMALL($A1:$G1,3) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "lindsayr" wrote in message ... When I enter in the formula - no change occurs..... Some rows maybe 20 prices, and some may have 10. What I ultimatley am looking to do, is rank the top 3 prices from cheapest, to 2nd cheapest, to 3rd cheapest and have each price highlighted a different color. Sorry if this is confusing.... :) I appreciate your help! "T. Valko" wrote: Assuming there will *always* be at least 3 prices... The prices are in the range A1:E1... Select the range A1:E1 Goto the menu FormatConditional Formatting Formula Is: =AND(A1<"",A1<=SMALL($A1:$E1,3)) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "lindsayr" wrote in message ... Yes, I want to highlight the 3 "lowest" prices, which will change daily. I have them linked to another page which will be updated everyday. "T. Valko" wrote: highlight the top 3 cheapest prices In other words, you want to highlight the 3 *lowest* prices? Ok, will there *always* be at least 3 prices? Where are these prices? -- Biff Microsoft Excel MVP "lindsayr" wrote in message ... I have created a pricing matrix in Excel 2003 and I am trying to use the conditional formatting function to highlight the top 3 cheapest prices in each row. I have tried to set up the formatting, but I continue to get an error message that says "Change the reference to a single cell, or use the reference with a worksheet function." I'm a bit of a novice when it comes to this program so any ideas would be appreciated. Thanks, |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting Question
You're welocme. Thanks for the feedback!
-- Biff Microsoft Excel MVP "lindsayr" wrote in message ... It worked!!! Thank you SO much for your help!! Lindsay "T. Valko" wrote: Ok... Let's assume the range of interest is A1:G5. Not all rows will have the same amount of numbers in them but the last column that might have a number is column G. Select the range A1:G5 Goto the menu formatConditional Formatting Condition 1 Formula Is: =AND(A1<"",A1=MIN($A1:$G1)) Click the Format button Select the desired style(s) OK Click the Add button Condition 2 Formula Is: =A1=SMALL($A1:$G1,2) Click the Format button Select the desired style(s) OK Click the Add button Condition 3 Formula Is: =A1=SMALL($A1:$G1,3) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "lindsayr" wrote in message ... When I enter in the formula - no change occurs..... Some rows maybe 20 prices, and some may have 10. What I ultimatley am looking to do, is rank the top 3 prices from cheapest, to 2nd cheapest, to 3rd cheapest and have each price highlighted a different color. Sorry if this is confusing.... :) I appreciate your help! "T. Valko" wrote: Assuming there will *always* be at least 3 prices... The prices are in the range A1:E1... Select the range A1:E1 Goto the menu FormatConditional Formatting Formula Is: =AND(A1<"",A1<=SMALL($A1:$E1,3)) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "lindsayr" wrote in message ... Yes, I want to highlight the 3 "lowest" prices, which will change daily. I have them linked to another page which will be updated everyday. "T. Valko" wrote: highlight the top 3 cheapest prices In other words, you want to highlight the 3 *lowest* prices? Ok, will there *always* be at least 3 prices? Where are these prices? -- Biff Microsoft Excel MVP "lindsayr" wrote in message ... I have created a pricing matrix in Excel 2003 and I am trying to use the conditional formatting function to highlight the top 3 cheapest prices in each row. I have tried to set up the formatting, but I continue to get an error message that says "Change the reference to a single cell, or use the reference with a worksheet function." I'm a bit of a novice when it comes to this program so any ideas would be appreciated. Thanks, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formatting Question | Excel Discussion (Misc queries) | |||
| Conditional formatting question | Excel Worksheet Functions | |||
| Conditional Formatting Question | Excel Worksheet Functions | |||
| If/Then and conditional formatting question | Excel Discussion (Misc queries) | |||
| conditional formatting question | Excel Worksheet Functions | |||
Linear Mode
