Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formating / linked cells? | Excel Discussion (Misc queries) | |||
Conditional formatting...cont. from 9/25 | Excel Discussion (Misc queries) | |||
how do I maintain my conditional formatting when filtering cells? | Excel Worksheet Functions | |||
How do i count the number of conditional formatted cells? | Excel Discussion (Misc queries) | |||
Return number of cells filled | New Users to Excel |