Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Guidance/Help to Highlight Cell contentts Based on Min/Max
Today, I went shopping for best price for a gallon of heating oil, and poked the quoted prices for 200, 300, 400, and 500 gallons planned purchase into my existing XLS spreadsheet. Today's collected data is organized like this: Col A Col B Col C Col D Col E Col F Col G Col H Col I Col J Col K Row1 Vendor Phone # blank 200 200 Cost 300 300 Cost 400 400 Cost 500 500 Cost Row2 3.699 $739.80 3.679 $1,103.70 3.659 $1,463.60 3.599 $1,799.50 Row3 3.619 $723.80 3.609 $1,082.70 3.609 $1,443.60 3.589 $1,794.50 Row4 3.519 $703.80 3.509 $1,052.70 3.509 $1,403.60 3.499 $1,749.50 Row5 3.539 $707.80 3.519 $1,055.70 3.499 $1,399.60 3.479 $1,739.50 Row6 3.349 $669.80 3.349 $1,004.70 3.349 $1,339.60 3.349 $1,674.50 Row7 3.699 $739.80 3.679 $1,103.70 3.659 $1,463.60 3.599 $1,799.50 Row8 3.499 $699.80 3.489 $1,046.70 3.489 $1,395.60 3.489 $1,744.50 Row9 3.449 $689.80 3.429 $1,028.70 3.429 $1,371.60 3.409 $1,704.50 Row10 3.599 $719.80 3.589 $1,076.70 3.589 $1,435.60 3.569 $1,784.50 Row11 3.509 $701.80 3.509 $1,052.70 3.579 $1,431.60 3.579 $1,789.50 Row12 3.509 $701.80 3.509 $1,052.70 3.579 $1,431.60 3.579 $1,789.50 Row13 3.749 $749.80 3.699 $1,109.70 3.699 $1,479.60 3.649 $1,824.50 Row14 3.699 $739.80 3.679 $1,103.70 3.659 $1,463.60 3.599 $1,799.50 Row15 .. .. .. Row22 BEST $ Price: 3.349 3.349 3.349 3.349 I have identified the best quoted price using the formula: =MIN(D2:D17) which is copied into F22, H22, and J22. Should I "NAME" the cell(s) that contains the BEST $ Price result, because sometime in the future I most likely will want to do some charting based on the collected data, since I track the same vendors when shopping for best available heating fuel price based on quantity of purchased gallons? Is there a function that I could use to locate and highlight the cell(s) that contain the BEST $ Price value(s)? In this particular shopping instance, there is more than one cell that contains the "Best $ Price" result. The highlighting can be a blinking cell or a choosen color or perhaps change the font size/make bold for the target cell to one that is larger than all others. I hope that the above displays properly. This is my first attempt at posting this info to the NG. Thanks. Regards, Vince Long Island, NY |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Guidance/Help to Highlight Cell contentts Based on Min/Max
You should look into using conditional formatting (on the Format
menu). This allows you to change colour, font size, embolden etc, depending on some condition (in your case Cell Value Is, =BEST). Try it out. Hope this helps. Pete On May 3, 1:53*am, Vince wrote: Today, I went shopping for best price for a gallon of heating oil, and poked the quoted prices for 200, 300, 400, and 500 gallons planned purchase into my existing XLS spreadsheet. * Today's collected data is organized like this: * * * * Col A * * * * * Col B * * * * * Col C * Col D * Col E Col F Col G * * * * * Col H * Col I * * * * * Col J * Col K Row1 * *Vendor * * * * *Phone # blank * 200 * * *200 Cost * * * 300 300 Cost * * * *400 * * *400 Cost * * * 500 * * *500 Cost Row2 * * * * * * * * * * * * * * * * * * * * * *3.699 * *$739.80 3.679 $1,103.70 * * * 3.659 * *$1,463.60 * * *3.599 * *$1,799.50 Row3 * * * * * * * * * * * * * * * * * * * * * *3.619 * *$723.80 3.609 $1,082.70 * * * 3.609 * *$1,443.60 * * *3.589 * *$1,794.50 Row4 * * * * * * * * * * * * * * * * * * * * * *3.519 * *$703.80 3.509 $1,052.70 * * * 3.509 * *$1,403.60 * * *3.499 * *$1,749.50 Row5 * * * * * * * * * * * * * * * * * * * * * *3.539 * *$707.80 3.519 $1,055.70 * * * 3.499 * *$1,399.60 * * *3.479 * *$1,739.50 Row6 * * * * * * * * * * * * * * * * * * * * * *3.349 * *$669.80 3.349 $1,004.70 * * * 3.349 * *$1,339.60 * * *3.349 * *$1,674.50 Row7 * * * * * * * * * * * * * * * * * * * * * *3.699 * *$739.80 3.679 $1,103.70 * * * 3.659 * *$1,463.60 * * *3.599 * *$1,799.50 Row8 * * * * * * * * * * * * * * * * * * * * * *3.499 * *$699.80 3.489 $1,046.70 * * * 3.489 * *$1,395.60 * * *3.489 * *$1,744.50 Row9 * * * * * * * * * * * * * * * * * * * * * *3.449 * *$689.80 3.429 $1,028.70 * * * 3.429 * *$1,371.60 * * *3.409 * *$1,704.50 Row10 * * * * * * * * * * * * * * * * * * * * * 3.599 * *$719.80 3.589 $1,076.70 * * * 3.589 * *$1,435.60 * * *3.569 * *$1,784.50 Row11 * * * * * * * * * * * * * * * * * * * * * 3.509 * *$701.80 3.509 $1,052.70 * * * 3.579 * *$1,431.60 * * *3.579 * *$1,789.50 Row12 * * * * * * * * * * * * * * * * * * * * * 3.509 * *$701.80 3.509 $1,052.70 * * * 3.579 * *$1,431.60 * * *3.579 * *$1,789.50 Row13 * * * * * * * * * * * * * * * * * * * * * 3.749 * *$749.80 3.699 $1,109.70 * * * 3.699 * *$1,479.60 * * *3.649 * *$1,824.50 Row14 * * * * * * * * * * * * * * * * * * * * * 3.699 * *$739.80 3.679 $1,103.70 * * * 3.659 * *$1,463.60 * * *3.599 * *$1,799.50 Row15 . . . Row22 * * * * * * * * * BEST $ Price: * * * * * 3.349 3.349 3.349 * * * * * * * * * 3.349 * I have identified the best quoted price using the formula: =MIN(D2:D17) which is copied into F22, H22, and J22. Should I "NAME" the cell(s) that contains the BEST $ Price result, because sometime in the future I most likely will want to do some charting based on the collected data, since I track the same vendors when shopping for best available heating fuel price based on quantity of purchased gallons? Is there a function that I could use to locate and highlight the cell(s) that contain the BEST $ Price value(s)? *In this particular shopping instance, there is more than one cell that contains the "Best $ Price" result. *The highlighting can be a blinking cell or a choosen color or perhaps change the font size/make bold for the target cell to one that is larger than all others. I hope that the above displays properly. This is my first attempt at posting this info to the NG. * *Thanks. Regards, Vince Long Island, NY |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Guidance/Help to Highlight Cell contentts Based on Min/Max
I agree, conditional formatting would be useful in this scenario, however I
would begin with the Min formula i.e at bottom of each column under cost enter the formula =min(range:range) [note you need to place in your own range i.e. B2:B10] this will then give you the answer to which is the lowest value and under FormatConditional Format select Equal to A11 {A11 being the cell with the formula =MIN(B2:B10) Hope this helps. Tanya "Pete_UK" wrote: You should look into using conditional formatting (on the Format menu). This allows you to change colour, font size, embolden etc, depending on some condition (in your case Cell Value Is, =BEST). Try it out. Hope this helps. Pete On May 3, 1:53 am, Vince wrote: Today, I went shopping for best price for a gallon of heating oil, and poked the quoted prices for 200, 300, 400, and 500 gallons planned purchase into my existing XLS spreadsheet. Today's collected data is organized like this: Col A Col B Col C Col D Col E Col F Col G Col H Col I Col J Col K Row1 Vendor Phone # blank 200 200 Cost 300 300 Cost 400 400 Cost 500 500 Cost Row2 3.699 $739.80 3.679 $1,103.70 3.659 $1,463.60 3.599 $1,799.50 Row3 3.619 $723.80 3.609 $1,082.70 3.609 $1,443.60 3.589 $1,794.50 Row4 3.519 $703.80 3.509 $1,052.70 3.509 $1,403.60 3.499 $1,749.50 Row5 3.539 $707.80 3.519 $1,055.70 3.499 $1,399.60 3.479 $1,739.50 Row6 3.349 $669.80 3.349 $1,004.70 3.349 $1,339.60 3.349 $1,674.50 Row7 3.699 $739.80 3.679 $1,103.70 3.659 $1,463.60 3.599 $1,799.50 Row8 3.499 $699.80 3.489 $1,046.70 3.489 $1,395.60 3.489 $1,744.50 Row9 3.449 $689.80 3.429 $1,028.70 3.429 $1,371.60 3.409 $1,704.50 Row10 3.599 $719.80 3.589 $1,076.70 3.589 $1,435.60 3.569 $1,784.50 Row11 3.509 $701.80 3.509 $1,052.70 3.579 $1,431.60 3.579 $1,789.50 Row12 3.509 $701.80 3.509 $1,052.70 3.579 $1,431.60 3.579 $1,789.50 Row13 3.749 $749.80 3.699 $1,109.70 3.699 $1,479.60 3.649 $1,824.50 Row14 3.699 $739.80 3.679 $1,103.70 3.659 $1,463.60 3.599 $1,799.50 Row15 . . . Row22 BEST $ Price: 3.349 3.349 3.349 3.349 I have identified the best quoted price using the formula: =MIN(D2:D17) which is copied into F22, H22, and J22. Should I "NAME" the cell(s) that contains the BEST $ Price result, because sometime in the future I most likely will want to do some charting based on the collected data, since I track the same vendors when shopping for best available heating fuel price based on quantity of purchased gallons? Is there a function that I could use to locate and highlight the cell(s) that contain the BEST $ Price value(s)? In this particular shopping instance, there is more than one cell that contains the "Best $ Price" result. The highlighting can be a blinking cell or a choosen color or perhaps change the font size/make bold for the target cell to one that is larger than all others. I hope that the above displays properly. This is my first attempt at posting this info to the NG. Thanks. Regards, Vince Long Island, NY |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
highlight the cell based on the range for a given value | Excel Discussion (Misc queries) | |||
Macro to highlight cells based on content | Excel Worksheet Functions | |||
how do I highlight an entire row based on the value of its content | Excel Discussion (Misc queries) | |||
Highlight cells with ctrl-click but only un-highlight one cell | Excel Discussion (Misc queries) | |||
Isolate rows based on highlight (Excel 2003) | Excel Worksheet Functions |