Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 247
Default Return Min & Max value of Conditional Formatted cells

Hi All,

Is this possible with a Formula?...

I would like to retrieve the Minimum and Maximum from numerous columns with
cells that already have Conditional Formatting applied to them. The CF
formats the cell interior red. Can the Min and Max values be found within the
CF red interior cells and returned to an empty cell.

Thanks,
Sam

--
Message posted via http://www.officekb.com

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Return Min & Max value of Conditional Formatted cells

See http://www.xldynamic.com/source/xld.ColourCounter.html

--
HTH

Bob Phillips

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

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:65cb4431d96cf@uwe...
Hi All,

Is this possible with a Formula?...

I would like to retrieve the Minimum and Maximum from numerous columns

with
cells that already have Conditional Formatting applied to them. The CF
formats the cell interior red. Can the Min and Max values be found within

the
CF red interior cells and returned to an empty cell.

Thanks,
Sam

--
Message posted via http://www.officekb.com



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 247
Default Return Min & Max value of Conditional Formatted cells

Hi Bob,

Thank you very much for your assistance.

I've had a read through the text entitled Processing Coloured Cells.
Unfortunately, I think I'm still stuck based on the quoted constraint listed:

"the second shortcoming is that this technique at present does not cater for
cells that are coloured due to conditional formatting."

I think this excludes my scenario from using any of the techniques. Have I
understood this correctly?

Cheers,
Sam



Bob Phillips wrote:
See http://www.xldynamic.com/source/xld.ColourCounter.html

Hi All,

[quoted text clipped - 7 lines]
Thanks,
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200609/1

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Return Min & Max value of Conditional Formatted cells

You have understood correctly. There is another paper on CF there
http://www.xldynamic.com/source/xld.CFConditions.html

As to the quoted constraint, if you build it into SUMPRODUCT, you can add
another condition there.
--
HTH

Bob Phillips

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

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:65cbf4a6b5e73@uwe...
Hi Bob,

Thank you very much for your assistance.

I've had a read through the text entitled Processing Coloured Cells.
Unfortunately, I think I'm still stuck based on the quoted constraint

listed:

"the second shortcoming is that this technique at present does not cater

for
cells that are coloured due to conditional formatting."

I think this excludes my scenario from using any of the techniques. Have I
understood this correctly?

Cheers,
Sam



Bob Phillips wrote:
See http://www.xldynamic.com/source/xld.ColourCounter.html

Hi All,

[quoted text clipped - 7 lines]
Thanks,
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200609/1



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default Return Min & Max value of Conditional Formatted cells

If the cells have cf applied to them then you should be able to write a
formula based on the logic that has been used in the cf'ing to find the
min/max.

Why are the cells formatted red?

Biff

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:65cb4431d96cf@uwe...
Hi All,

Is this possible with a Formula?...

I would like to retrieve the Minimum and Maximum from numerous columns
with
cells that already have Conditional Formatting applied to them. The CF
formats the cell interior red. Can the Min and Max values be found within
the
CF red interior cells and returned to an empty cell.

Thanks,
Sam

--
Message posted via http://www.officekb.com





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 247
Default Return Min & Max value of Conditional Formatted cells

Hi Biff,

This is the Conditional Format Formula =COUNTIF(INDEX(Data,MATCH($H$13,ID,0),
0),$G17)

The cells are formatted red when a specific ID is matched and its associated
numeric value in the Dynamic Named Range "Data"

Cheers,
Sam

Biff wrote:
If the cells have cf applied to them then you should be able to write a
formula based on the logic that has been used in the cf'ing to find the
min/max.


Why are the cells formatted red?


Biff

Hi All,

[quoted text clipped - 9 lines]
Thanks,
Sam


--
Message posted via http://www.officekb.com

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default Return Min & Max value of Conditional Formatted cells

Need the details. A small chunk of sample data with expected result would
help.

Biff

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:65cdea6908411@uwe...
Hi Biff,

This is the Conditional Format Formula
=COUNTIF(INDEX(Data,MATCH($H$13,ID,0),
0),$G17)

The cells are formatted red when a specific ID is matched and its
associated
numeric value in the Dynamic Named Range "Data"

Cheers,
Sam

Biff wrote:
If the cells have cf applied to them then you should be able to write a
formula based on the logic that has been used in the cf'ing to find the
min/max.


Why are the cells formatted red?


Biff

Hi All,

[quoted text clipped - 9 lines]
Thanks,
Sam


--
Message posted via http://www.officekb.com



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 247
Default Return Min & Max value of Conditional Formatted cells

Hi Biff,

Sample Data using ID 10621

Cell Ref H13 = 10621
Column "G" = Numeric Labels
Column "H" = Frequency Values

Column "G" Column"H"
Numeric Label Frequency
Row17 90 10
Row18 480 7
Row19 80 5
Row20 60 4
Row21 50 4
Row22 470 3
Row23 430 2
Row24 420 1


ID 10621 will have several Numeric Labels in its row. If any of the above
Numeric Labels match with ID 10621 associated row values then the Numeric
Label's adjacent Frequency value should be highlighted in Red.

The Numeric Labels are defined in a 12 column by many rows Dynamic Named
Range called "Data".

This is the Conditional Format Formula
=COUNTIF(INDEX(Data,MATCH($H$13,ID,0),0),$G17)

CF Formula applied to column "H" (Frequency values)


Cheers,
Sam

Biff wrote:
Need the details. A small chunk of sample data with expected result would
help.


Biff


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200609/1

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 247
Default Return Min & Max value of Conditional Formatted cells

Hi Bob,

Thanks again for input. I've read through the paper starting with Testing CF
Conditions and tried your neat Functions CFColorCount at
http://www.xldynamic.com/source/xld.CFConditions.html
and ColorIndex at http://www.xldynamic.com/source/xld.ColourCounter.html

I'm trying to work with your SUMPRODUCT suggestion:
As to the quoted constraint, if you build it into SUMPRODUCT, you can add another condition there.


Is using the CFColorCount Function with SUMPRODUCT part of the solution?
Cannot get required result.

Further help appreciated.

Cheers,
Sam

Bob Phillips wrote:
You have understood correctly. There is another paper on CF there
http://www.xldynamic.com/source/xld.CFConditions.html

As to the quoted constraint, if you build it into SUMPRODUCT, you can add
another condition there.
Hi Bob,

[quoted text clipped - 19 lines]
Thanks,
Sam


--
Message posted via http://www.officekb.com

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default Return Min & Max value of Conditional Formatted cells

Well, this is something I would need to see for myself. I'm just not getting
a good "picture" of the data. If you want to/can send me a copy of the file
I'll get a better idea of what needs to be done. I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:65cf486263e96@uwe...
Hi Biff,

Sample Data using ID 10621

Cell Ref H13 = 10621
Column "G" = Numeric Labels
Column "H" = Frequency Values

Column "G" Column"H"
Numeric Label Frequency
Row17 90 10
Row18 480 7
Row19 80 5
Row20 60 4
Row21 50 4
Row22 470 3
Row23 430 2
Row24 420 1


ID 10621 will have several Numeric Labels in its row. If any of the above
Numeric Labels match with ID 10621 associated row values then the Numeric
Label's adjacent Frequency value should be highlighted in Red.

The Numeric Labels are defined in a 12 column by many rows Dynamic Named
Range called "Data".

This is the Conditional Format Formula
=COUNTIF(INDEX(Data,MATCH($H$13,ID,0),0),$G17)

CF Formula applied to column "H" (Frequency values)


Cheers,
Sam

Biff wrote:
Need the details. A small chunk of sample data with expected result would
help.


Biff


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200609/1





  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Return Min & Max value of Conditional Formatted cells

Sam,

I am going to the cricket international today, so I will take a look
tomorrow.

--
HTH

Bob Phillips

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

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:65cfb43c50122@uwe...
Hi Bob,

Thanks again for input. I've read through the paper starting with Testing

CF
Conditions and tried your neat Functions CFColorCount at
http://www.xldynamic.com/source/xld.CFConditions.html
and ColorIndex at http://www.xldynamic.com/source/xld.ColourCounter.html

I'm trying to work with your SUMPRODUCT suggestion:
As to the quoted constraint, if you build it into SUMPRODUCT, you can add

another condition there.

Is using the CFColorCount Function with SUMPRODUCT part of the solution?
Cannot get required result.

Further help appreciated.

Cheers,
Sam

Bob Phillips wrote:
You have understood correctly. There is another paper on CF there
http://www.xldynamic.com/source/xld.CFConditions.html

As to the quoted constraint, if you build it into SUMPRODUCT, you can add
another condition there.
Hi Bob,

[quoted text clipped - 19 lines]
Thanks,
Sam


--
Message posted via http://www.officekb.com



  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 247
Default Return Min & Max value of Conditional Formatted cells

Hi Biff,

Thanks for reply. I may have lost something in the translation of that last
posting.

Column "H" with the numeric values is already using this CF Formula
=COUNTIF(INDEX(Data,MATCH($H$13,ID,0),0),$G17)
with red interior cell Format applied when the condition is satisfied.

The cells of numeric values that satisfy the CF Formula in column "H" are
red.

I would like to extract the minimum and maximum values in Column "H" from the
cells that satsify the CF.

Cheers,
Sam


Biff wrote:
Well, this is something I would need to see for myself. I'm just not getting
a good "picture" of the data. If you want to/can send me a copy of the file
I'll get a better idea of what needs to be done. I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

Hi Biff,

[quoted text clipped - 34 lines]

Biff


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200609/1

  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 256
Default Return Min & Max value of Conditional Formatted cells

Maybe...

=MAX(IF(ISNUMBER(MATCH(G17:G24,INDEX(Data,MATCH(H1 3,ID,0),0),0)),H17:H24)
)

and

=MIN(IF(ISNUMBER(MATCH(G17:G24,INDEX(Data,MATCH(H1 3,ID,0),0),0)),H17:H24)
)

Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER.

Hope this helps!

In article <65d575e573e78@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Biff,

Thanks for reply. I may have lost something in the translation of that last
posting.

Column "H" with the numeric values is already using this CF Formula
=COUNTIF(INDEX(Data,MATCH($H$13,ID,0),0),$G17)
with red interior cell Format applied when the condition is satisfied.

The cells of numeric values that satisfy the CF Formula in column "H" are
red.

I would like to extract the minimum and maximum values in Column "H" from the
cells that satsify the CF.

Cheers,
Sam

  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 247
Default Return Min & Max value of Conditional Formatted cells

Hi Domenic,

Thank you very much. That's Great! Works a treat!


Cheers,
Sam

Domenic wrote:
Maybe...


=MAX(IF(ISNUMBER(MATCH(G17:G24,INDEX(Data,MATCH(H 13,ID,0),0),0)),H17:H24))


and


=MIN(IF(ISNUMBER(MATCH(G17:G24,INDEX(Data,MATCH(H 13,ID,0),0),0)),H17:H24))


Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER.


Hope this helps!

Hi Biff,

[quoted text clipped - 13 lines]
Cheers,
Sam


--
Message posted via http://www.officekb.com

  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 247
Default Return Min & Max value of Conditional Formatted cells

Hi Domenic,

Your solution works great.

Is it possible to also have a Formula that will return the respective Numeric
Label for each of the minimum and maximum values. The Numeric Label is
housed adjacent to each Numeric Value. Numeric Label in column "G", Numeric
Value in column "H".

Cheers,
Sam

Domenic wrote:
Maybe...


=MAX(IF(ISNUMBER(MATCH(G17:G24,INDEX(Data,MATCH(H 13,ID,0),0),0)),H17:H24))


and


=MIN(IF(ISNUMBER(MATCH(G17:G24,INDEX(Data,MATCH(H 13,ID,0),0),0)),H17:H24))


Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER.


Hope this helps!


Hi Biff,

[quoted text clipped - 13 lines]
Cheers,
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200609/1



  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 256
Default Return Min & Max value of Conditional Formatted cells

Assuming that J13 and J14 contain the formulas returning the maximum and
minimum values, try...

=INDEX(G17:G24,MATCH(J13,H17:H24,0))

and

=INDEX(G17:G24,MATCH(J14,H17:H24,0))

Note that the formula will only return the first occurrence. Post back
if there can be more than one occurrence of maximum and minimum values.

Hope this helps!

In article <65d6f0b6cd71d@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

Your solution works great.

Is it possible to also have a Formula that will return the respective Numeric
Label for each of the minimum and maximum values. The Numeric Label is
housed adjacent to each Numeric Value. Numeric Label in column "G", Numeric
Value in column "H".

Cheers,
Sam

Domenic wrote:
Maybe...


=MAX(IF(ISNUMBER(MATCH(G17:G24,INDEX(Data,MATCH(H 13,ID,0),0),0)),H17:H24))


and


=MIN(IF(ISNUMBER(MATCH(G17:G24,INDEX(Data,MATCH(H 13,ID,0),0),0)),H17:H24))


Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER.


Hope this helps!


Hi Biff,

[quoted text clipped - 13 lines]
Cheers,
Sam

  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 247
Default Return Min & Max value of Conditional Formatted cells

Hi Domenic,

The Formulas below do return Numeric Labels for minimum and maximum values
but they are not the Numeric Labels for the actual minimum and maximum
Numeric Values that have the CF Formula applied to them.

Further help most appreciated.

Cheers,
Sam

Domenic wrote:
Assuming that J13 and J14 contain the formulas returning the maximum and
minimum values, try...


=INDEX(G17:G24,MATCH(J13,H17:H24,0))


and


=INDEX(G17:G24,MATCH(J14,H17:H24,0))


Note that the formula will only return the first occurrence. Post back
if there can be more than one occurrence of maximum and minimum values.


Hope this helps!

Hi Domenic,

[quoted text clipped - 26 lines]
Cheers,
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200609/1

  #18   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 256
Default Return Min & Max value of Conditional Formatted cells

Sorry! Try the following instead...

=INDEX(G17:G24,MATCH(J13,IF(ISNUMBER(MATCH(G17:G24 ,INDEX(Data,MATCH(H13,I
D,0),0),0)),H17:H24),0))

and

=INDEX(G17:G24,MATCH(J14,IF(ISNUMBER(MATCH(G17:G24 ,INDEX(Data,MATCH(H13,I
D,0),0),0)),H17:H24),0))

Both formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <65d7dd104fcb6@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

The Formulas below do return Numeric Labels for minimum and maximum values
but they are not the Numeric Labels for the actual minimum and maximum
Numeric Values that have the CF Formula applied to them.

Further help most appreciated.

Cheers,
Sam

  #19   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 247
Default Return Min & Max value of Conditional Formatted cells

Hi Domenic,

Perfect! Thank you once again.

Cheers,
Sam

Domenic wrote:
Sorry! Try the following instead...


=INDEX(G17:G24,MATCH(J13,IF(ISNUMBER(MATCH(G17:G2 4,INDEX(Data,MATCH(H13,I
D,0),0),0)),H17:H24),0))


and


=INDEX(G17:G24,MATCH(J14,IF(ISNUMBER(MATCH(G17:G2 4,INDEX(Data,MATCH(H13,ID,0),0),0)),H17:H24),0))


Both formulas need to be confirmed with CONTROL+SHIFT+ENTER.


Hope this helps!


--
Message posted via http://www.officekb.com

  #20   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default Return Min & Max value of Conditional Formatted cells

I see things are under control here.........

Biff

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:65d848eca35c8@uwe...
Hi Domenic,

Perfect! Thank you once again.

Cheers,
Sam

Domenic wrote:
Sorry! Try the following instead...


=INDEX(G17:G24,MATCH(J13,IF(ISNUMBER(MATCH(G17:G 24,INDEX(Data,MATCH(H13,I
D,0),0),0)),H17:H24),0))


and


=INDEX(G17:G24,MATCH(J14,IF(ISNUMBER(MATCH(G17:G 24,INDEX(Data,MATCH(H13,ID,0),0),0)),H17:H24),0))


Both formulas need to be confirmed with CONTROL+SHIFT+ENTER.


Hope this helps!


--
Message posted via http://www.officekb.com





  #21   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 256
Default Return Min & Max value of Conditional Formatted cells

In article ,
"Biff" wrote:

I see things are under control here.........

Biff


With a little bit of a detour on my part... :)
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
conditional formating / linked cells? ET Excel Discussion (Misc queries) 7 December 7th 05 07:57 PM
Conditional formatting...cont. from 9/25 Guenzak Excel Discussion (Misc queries) 4 September 26th 05 10:55 PM
how do I maintain my conditional formatting when filtering cells? Aja Excel Worksheet Functions 0 September 21st 05 09:56 PM
How do i count the number of conditional formatted cells? kate Excel Discussion (Misc queries) 2 May 16th 05 10:47 PM
Return number of cells filled LMB New Users to Excel 3 April 29th 05 02:55 AM


All times are GMT +1. The time now is 02:12 PM.

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

About Us

"It's about Microsoft Excel"