A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Conditional Formatting Using Min/Max



 
 
Thread Tools Display Modes
  #1  
Old April 22nd, 2009, 04:57 PM posted to microsoft.public.excel.worksheet.functions
CE
external usenet poster
 
Posts: 19
Default Conditional Formatting Using Min/Max

Regarding Excel 2003, I want to indicate the min value in a non-contiguous
range of cells. The data are located in cells G8, K8, O8, S8, W8, AA8 and
each cell contains a formula. So far I've tried the following formulas but
without success.
1) =MIN(G8,K8,O8,S8,W8,AA8)
2) =G8=MIN($G8,$K8,$O8,$S8,$W8,$AA8)
3) =G8=MIN($G$8,$K$8,$O$8,$S$8,$W$8,$AA$8)

Please help!
  #2  
Old April 22nd, 2009, 05:45 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Conditional Formatting Using Min/Max

=G8=MIN($G8,$K8,$O8,$S8,$W8,$AA8)
=G8=MIN($G$8,$K$8,$O$8,$S$8,$W$8,$AA$8)


Those formuls are correct. Select the cells from RIGHT to LEFT then try it.

--
Biff
Microsoft Excel MVP


"CE" wrote in message
...
Regarding Excel 2003, I want to indicate the min value in a non-contiguous
range of cells. The data are located in cells G8, K8, O8, S8, W8, AA8 and
each cell contains a formula. So far I've tried the following formulas but
without success.
1) =MIN(G8,K8,O8,S8,W8,AA8)
2) =G8=MIN($G8,$K8,$O8,$S8,$W8,$AA8)
3) =G8=MIN($G$8,$K$8,$O$8,$S$8,$W$8,$AA$8)

Please help!



  #3  
Old April 22nd, 2009, 05:46 PM posted to microsoft.public.excel.worksheet.functions
Luke M
external usenet poster
 
Posts: 2,672
Default Conditional Formatting Using Min/Max

Make sure you're using "cell value is", and "Equal to"
You can then input into the box:

=MIN(G8,K8,O8,S8,W8,AA8)

If it's still not working, perhaps check to make sure all cells in formula
contain at least 1 number (not number formatted as text) and the cell you are
formatting also contains a number.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"CE" wrote:

Regarding Excel 2003, I want to indicate the min value in a non-contiguous
range of cells. The data are located in cells G8, K8, O8, S8, W8, AA8 and
each cell contains a formula. So far I've tried the following formulas but
without success.
1) =MIN(G8,K8,O8,S8,W8,AA8)
2) =G8=MIN($G8,$K8,$O8,$S8,$W8,$AA8)
3) =G8=MIN($G$8,$K$8,$O$8,$S$8,$W$8,$AA$8)

Please help!

  #4  
Old April 22nd, 2009, 06:10 PM posted to microsoft.public.excel.worksheet.functions
CE
external usenet poster
 
Posts: 19
Default Conditional Formatting Using Min/Max

Your post gave me an idea. I substituted the G8 with AA8 and it worked! I did
it again to the next row to varify. Can you please tell me how to best copy
this to my additional 583 rows of data?

Thanks

"T. Valko" wrote:

=G8=MIN($G8,$K8,$O8,$S8,$W8,$AA8)
=G8=MIN($G$8,$K$8,$O$8,$S$8,$W$8,$AA$8)


Those formuls are correct. Select the cells from RIGHT to LEFT then try it.

--
Biff
Microsoft Excel MVP


"CE" wrote in message
...
Regarding Excel 2003, I want to indicate the min value in a non-contiguous
range of cells. The data are located in cells G8, K8, O8, S8, W8, AA8 and
each cell contains a formula. So far I've tried the following formulas but
without success.
1) =MIN(G8,K8,O8,S8,W8,AA8)
2) =G8=MIN($G8,$K8,$O8,$S8,$W8,$AA8)
3) =G8=MIN($G$8,$K$8,$O$8,$S$8,$W$8,$AA$8)

Please help!




  #5  
Old April 22nd, 2009, 06:27 PM posted to microsoft.public.excel.worksheet.functions
David Biddulph
external usenet poster
 
Posts: 8,714
Default Conditional Formatting Using Min/Max

In what way was formula 1 "without success"? What values were in G8, K8,
O8, S8, W8, and AA8, and what result did the formula give in your results
cell?

Formulae 2 and 3 should return either TRUE or FALSE, depending on whether or
not G8 was the minimum.
--
David Biddulph

"CE" wrote in message
...
Regarding Excel 2003, I want to indicate the min value in a non-contiguous
range of cells. The data are located in cells G8, K8, O8, S8, W8, AA8 and
each cell contains a formula. So far I've tried the following formulas but
without success.
1) =MIN(G8,K8,O8,S8,W8,AA8)
2) =G8=MIN($G8,$K8,$O8,$S8,$W8,$AA8)
3) =G8=MIN($G$8,$K$8,$O$8,$S$8,$W$8,$AA$8)

Please help!



  #6  
Old April 22nd, 2009, 06:32 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Conditional Formatting Using Min/Max

When you select a *contiguous* range of cells the top left cell is the
active cell. The active cell is the one cell in the selected range that
*isn't* shaded. So the formula you enter is *relative* to the active cell.

When you select a range of non-contiguous cells the active cell is the
*last* cell of the selection and the formula will be relative to the active
cell. That's why your formula didn't work. You probably selected the cells
from left to right.

To apply this formatting to all those rows:

Select the range starting from AA8:AA583 (or whatever the last row is), then
W8:W583, S8:S583, etc, etc.

From RIGHT to LEFT so that when you're done selecting all the cells, cell G8
will be the active cell. Then use the formula:

=G8=MIN($G8,$K8,$O8,$S8,$W8,$AA8)


--
Biff
Microsoft Excel MVP


"CE" wrote in message
...
Your post gave me an idea. I substituted the G8 with AA8 and it worked! I
did
it again to the next row to varify. Can you please tell me how to best
copy
this to my additional 583 rows of data?

Thanks

"T. Valko" wrote:

=G8=MIN($G8,$K8,$O8,$S8,$W8,$AA8)
=G8=MIN($G$8,$K$8,$O$8,$S$8,$W$8,$AA$8)


Those formuls are correct. Select the cells from RIGHT to LEFT then try
it.

--
Biff
Microsoft Excel MVP


"CE" wrote in message
...
Regarding Excel 2003, I want to indicate the min value in a
non-contiguous
range of cells. The data are located in cells G8, K8, O8, S8, W8, AA8
and
each cell contains a formula. So far I've tried the following formulas
but
without success.
1) =MIN(G8,K8,O8,S8,W8,AA8)
2) =G8=MIN($G8,$K8,$O8,$S8,$W8,$AA8)
3) =G8=MIN($G$8,$K$8,$O$8,$S$8,$W$8,$AA$8)

Please help!






  #7  
Old April 22nd, 2009, 06:43 PM posted to microsoft.public.excel.worksheet.functions
CE
external usenet poster
 
Posts: 19
Default Conditional Formatting Using Min/Max

The selected fomatting, in this case red font, did not indicate a min value.

"David Biddulph" wrote:

In what way was formula 1 "without success"? What values were in G8, K8,
O8, S8, W8, and AA8, and what result did the formula give in your results
cell?

Formulae 2 and 3 should return either TRUE or FALSE, depending on whether or
not G8 was the minimum.
--
David Biddulph

"CE" wrote in message
...
Regarding Excel 2003, I want to indicate the min value in a non-contiguous
range of cells. The data are located in cells G8, K8, O8, S8, W8, AA8 and
each cell contains a formula. So far I've tried the following formulas but
without success.
1) =MIN(G8,K8,O8,S8,W8,AA8)
2) =G8=MIN($G8,$K8,$O8,$S8,$W8,$AA8)
3) =G8=MIN($G$8,$K$8,$O$8,$S$8,$W$8,$AA$8)

Please help!




  #8  
Old April 22nd, 2009, 07:07 PM posted to microsoft.public.excel.worksheet.functions
CE
external usenet poster
 
Posts: 19
Default Conditional Formatting Using Min/Max

Ok folks I now have the copy issue resolved however I do have another
question. I need my formula to return results greated than 0. How exactly do
I insert this into option 2 below?

"CE" wrote:

Regarding Excel 2003, I want to indicate the min value in a non-contiguous
range of cells. The data are located in cells G8, K8, O8, S8, W8, AA8 and
each cell contains a formula. So far I've tried the following formulas but
without success.
1) =MIN(G8,K8,O8,S8,W8,AA8)
2) =G8=MIN($G8,$K8,$O8,$S8,$W8,$AA8)
3) =G8=MIN($G$8,$K$8,$O$8,$S$8,$W$8,$AA$8)

Please help!

  #9  
Old April 22nd, 2009, 07:21 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Conditional Formatting Using Min/Max

Change the formula to:

=AND(G80,G8=MIN($G8,$K8,$O8,$S8,$W8,$AA8))

--
Biff
Microsoft Excel MVP


"CE" wrote in message
...
Ok folks I now have the copy issue resolved however I do have another
question. I need my formula to return results greated than 0. How exactly
do
I insert this into option 2 below?

"CE" wrote:

Regarding Excel 2003, I want to indicate the min value in a
non-contiguous
range of cells. The data are located in cells G8, K8, O8, S8, W8, AA8 and
each cell contains a formula. So far I've tried the following formulas
but
without success.
1) =MIN(G8,K8,O8,S8,W8,AA8)
2) =G8=MIN($G8,$K8,$O8,$S8,$W8,$AA8)
3) =G8=MIN($G$8,$K$8,$O$8,$S$8,$W$8,$AA$8)

Please help!



  #10  
Old April 22nd, 2009, 08:10 PM posted to microsoft.public.excel.worksheet.functions
CE
external usenet poster
 
Posts: 19
Default Conditional Formatting Using Min/Max

That works. Thanks for the assist.

"T. Valko" wrote:

Change the formula to:

=AND(G80,G8=MIN($G8,$K8,$O8,$S8,$W8,$AA8))

--
Biff
Microsoft Excel MVP


"CE" wrote in message
...
Ok folks I now have the copy issue resolved however I do have another
question. I need my formula to return results greated than 0. How exactly
do
I insert this into option 2 below?

"CE" wrote:

Regarding Excel 2003, I want to indicate the min value in a
non-contiguous
range of cells. The data are located in cells G8, K8, O8, S8, W8, AA8 and
each cell contains a formula. So far I've tried the following formulas
but
without success.
1) =MIN(G8,K8,O8,S8,W8,AA8)
2) =G8=MIN($G8,$K8,$O8,$S8,$W8,$AA8)
3) =G8=MIN($G$8,$K$8,$O$8,$S$8,$W$8,$AA$8)

Please help!




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:46 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.