Return Min & Max value of Conditional Formatted cells

[Sam via OfficeKB.com]

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

 

[Bob Phillips]

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]

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

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

Hi All,

[quoted text clipped - 7 lines]

Thanks,
Sam

 

[Bob Phillips]

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)

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

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

Hi All,

[quoted text clipped - 7 lines]

Thanks,
Sam

 

[Biff]

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]

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

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

 

[Biff]

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

Biff

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

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

 

[Sam via OfficeKB.com]

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

 

[Sam via OfficeKB.com]

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

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]

 

[Biff]

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

 

[Bob Phillips]

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)

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

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

 

[Sam via OfficeKB.com]

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

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]

 

[Domenic]

Maybe...

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

and

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

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

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

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


Cheers,
Sam

Maybe...



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

 

[Sam via OfficeKB.com]

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

Maybe...



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

 

[Domenic]

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,

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

Maybe...



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

 

[Sam via OfficeKB.com]

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

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



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]

 

[Domenic]

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!

 

[Sam via OfficeKB.com]

Hi Domenic,

Perfect! Thank you once again.

Cheers,
Sam

 

[Biff]

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

Biff