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. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Calculating the mode of a criteria-based range
You can't use a criteria range unless you are using D functions or Advanced
filter, describe what your criteria is/are and maybe somebody will be able to help -- Regards, Peo Sjoblom "Kathy L." wrote in message news Thank you. I wish there were more Dxxx functions! My problem (I think) is how do I reference the criteria range in the IF statement's Logical Test, if I work with your array function below? I'm having difficulty translating the array function below to my criteria range and raw data. "T. Valko" wrote: There is no "DMODE" function. If you want to use the MODE() function and reference the criteria range that's used by other D functions the criteria would have to be values only and not contain any operators. (although you could probably remove any operators within the formula but that just adds [undue] complexity. -- Biff Microsoft Excel MVP "Kathy L." Kathy wrote in message ... Hello, I have found this help useful, however I'm not quite getting this to work as expected. I can get this to work with the example below, however my file is using database controls used in other DCOUNT functions. For the criteria, I'd like to point it to the controls, which are based on a larger set of raw data. Perhaps it would be easier to reference my spreadsheet if I send to you? Please let me know if I can email you. Thank you very much. "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... That did exactly what I needed. Thanks a lot! "T. Valko" wrote: Try this array formula** : =MODE(IF(B1:B510,A1:A5)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... I have two columns of data. I want to calculate the mode of some cells in the first column - including only those cells whose corresponding cell in the second column has a value greater than 10. For example: 2 | 20 1 | 11 2 | 8 1 | 16 2 |3 Would return MODE(2,1,1) = 1. How do I pass the MODE() function only the criteria-filtered cells? Thanks! |
#12
|
|||
|
|||
Calculating the mode of a criteria-based range
I have one tab of raw data (several columns), which include 'Company',
'Business Group', 'Job Type' and 'Source Code'. Another tab is the control criteria, which include 'Company', 'Business Group' and 'Job Type'. The row below the criteria header includes a specific company, business group, and job type that match values in the raw data. Here is a sampling: Raw data: A B C D 1 Company Business Group Job Type Source Type Code 2 AFFILIATES CARD BUSINESS Marketing 12 3 CONSUMER SALES Retail 7 4 AFFILIATES CARD BUSINESS Marketing 7 5 FACILITIES FACILITIES Technician 5 6 AFFILIATES CARD BUSINESS Marketing 6 7 AFFILIATES CARD BUSINESS Marketing 6 Criteria page: A B C 1 Company Business Group Job Type 2 AFFILIATES CARD BUSINESS Marketing Note: The values for Company and Bus. Group criteria point to another cell with a different formula. The intent is a user will select a company, etc. and the data will populate for that company. Based on the criteria, I'd like to identify the source appearing most often. Result = 6 "T. Valko" wrote: How about providing some details? -- Biff Microsoft Excel MVP "Kathy L." wrote in message news Thank you. I wish there were more Dxxx functions! My problem (I think) is how do I reference the criteria range in the IF statement's Logical Test, if I work with your array function below? I'm having difficulty translating the array function below to my criteria range and raw data. "T. Valko" wrote: There is no "DMODE" function. If you want to use the MODE() function and reference the criteria range that's used by other D functions the criteria would have to be values only and not contain any operators. (although you could probably remove any operators within the formula but that just adds [undue] complexity. -- Biff Microsoft Excel MVP "Kathy L." Kathy wrote in message ... Hello, I have found this help useful, however I'm not quite getting this to work as expected. I can get this to work with the example below, however my file is using database controls used in other DCOUNT functions. For the criteria, I'd like to point it to the controls, which are based on a larger set of raw data. Perhaps it would be easier to reference my spreadsheet if I send to you? Please let me know if I can email you. Thank you very much. "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... That did exactly what I needed. Thanks a lot! "T. Valko" wrote: Try this array formula** : =MODE(IF(B1:B510,A1:A5)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... I have two columns of data. I want to calculate the mode of some cells in the first column - including only those cells whose corresponding cell in the second column has a value greater than 10. For example: 2 | 20 1 | 11 2 | 8 1 | 16 2 |3 Would return MODE(2,1,1) = 1. How do I pass the MODE() function only the criteria-filtered cells? Thanks! |
#13
|
|||
|
|||
Calculating the mode of a criteria-based range
Try something like this (array entered):
=MODE(IF(('Raw Data'!A1:A10=Criteria!A2)*('Raw Data'!B1:B10=Criteria!B2)*('Raw Data'!C1:C10=Criteria!C2),'Raw Data'!D110)) Note that if there is no mode (at least 2 instances) that meets all the criteria the formula returns an error. -- Biff Microsoft Excel MVP "Kathy L." wrote in message ... I have one tab of raw data (several columns), which include 'Company', 'Business Group', 'Job Type' and 'Source Code'. Another tab is the control criteria, which include 'Company', 'Business Group' and 'Job Type'. The row below the criteria header includes a specific company, business group, and job type that match values in the raw data. Here is a sampling: Raw data: A B C D 1 Company Business Group Job Type Source Type Code 2 AFFILIATES CARD BUSINESS Marketing 12 3 CONSUMER SALES Retail 7 4 AFFILIATES CARD BUSINESS Marketing 7 5 FACILITIES FACILITIES Technician 5 6 AFFILIATES CARD BUSINESS Marketing 6 7 AFFILIATES CARD BUSINESS Marketing 6 Criteria page: A B C 1 Company Business Group Job Type 2 AFFILIATES CARD BUSINESS Marketing Note: The values for Company and Bus. Group criteria point to another cell with a different formula. The intent is a user will select a company, etc. and the data will populate for that company. Based on the criteria, I'd like to identify the source appearing most often. Result = 6 "T. Valko" wrote: How about providing some details? -- Biff Microsoft Excel MVP "Kathy L." wrote in message news Thank you. I wish there were more Dxxx functions! My problem (I think) is how do I reference the criteria range in the IF statement's Logical Test, if I work with your array function below? I'm having difficulty translating the array function below to my criteria range and raw data. "T. Valko" wrote: There is no "DMODE" function. If you want to use the MODE() function and reference the criteria range that's used by other D functions the criteria would have to be values only and not contain any operators. (although you could probably remove any operators within the formula but that just adds [undue] complexity. -- Biff Microsoft Excel MVP "Kathy L." Kathy wrote in message ... Hello, I have found this help useful, however I'm not quite getting this to work as expected. I can get this to work with the example below, however my file is using database controls used in other DCOUNT functions. For the criteria, I'd like to point it to the controls, which are based on a larger set of raw data. Perhaps it would be easier to reference my spreadsheet if I send to you? Please let me know if I can email you. Thank you very much. "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... That did exactly what I needed. Thanks a lot! "T. Valko" wrote: Try this array formula** : =MODE(IF(B1:B510,A1:A5)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... I have two columns of data. I want to calculate the mode of some cells in the first column - including only those cells whose corresponding cell in the second column has a value greater than 10. For example: 2 | 20 1 | 11 2 | 8 1 | 16 2 |3 Would return MODE(2,1,1) = 1. How do I pass the MODE() function only the criteria-filtered cells? Thanks! |
#14
|
|||
|
|||
Calculating the mode of a criteria-based range
I think I finally have this working. Thank you VERY much!!!!
"T. Valko" wrote: Try something like this (array entered): =MODE(IF(('Raw Data'!A1:A10=Criteria!A2)*('Raw Data'!B1:B10=Criteria!B2)*('Raw Data'!C1:C10=Criteria!C2),'Raw Data'!D110)) Note that if there is no mode (at least 2 instances) that meets all the criteria the formula returns an error. -- Biff Microsoft Excel MVP "Kathy L." wrote in message ... I have one tab of raw data (several columns), which include 'Company', 'Business Group', 'Job Type' and 'Source Code'. Another tab is the control criteria, which include 'Company', 'Business Group' and 'Job Type'. The row below the criteria header includes a specific company, business group, and job type that match values in the raw data. Here is a sampling: Raw data: A B C D 1 Company Business Group Job Type Source Type Code 2 AFFILIATES CARD BUSINESS Marketing 12 3 CONSUMER SALES Retail 7 4 AFFILIATES CARD BUSINESS Marketing 7 5 FACILITIES FACILITIES Technician 5 6 AFFILIATES CARD BUSINESS Marketing 6 7 AFFILIATES CARD BUSINESS Marketing 6 Criteria page: A B C 1 Company Business Group Job Type 2 AFFILIATES CARD BUSINESS Marketing Note: The values for Company and Bus. Group criteria point to another cell with a different formula. The intent is a user will select a company, etc. and the data will populate for that company. Based on the criteria, I'd like to identify the source appearing most often. Result = 6 "T. Valko" wrote: How about providing some details? -- Biff Microsoft Excel MVP "Kathy L." wrote in message news Thank you. I wish there were more Dxxx functions! My problem (I think) is how do I reference the criteria range in the IF statement's Logical Test, if I work with your array function below? I'm having difficulty translating the array function below to my criteria range and raw data. "T. Valko" wrote: There is no "DMODE" function. If you want to use the MODE() function and reference the criteria range that's used by other D functions the criteria would have to be values only and not contain any operators. (although you could probably remove any operators within the formula but that just adds [undue] complexity. -- Biff Microsoft Excel MVP "Kathy L." Kathy wrote in message ... Hello, I have found this help useful, however I'm not quite getting this to work as expected. I can get this to work with the example below, however my file is using database controls used in other DCOUNT functions. For the criteria, I'd like to point it to the controls, which are based on a larger set of raw data. Perhaps it would be easier to reference my spreadsheet if I send to you? Please let me know if I can email you. Thank you very much. "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... That did exactly what I needed. Thanks a lot! "T. Valko" wrote: Try this array formula** : =MODE(IF(B1:B510,A1:A5)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... I have two columns of data. I want to calculate the mode of some cells in the first column - including only those cells whose corresponding cell in the second column has a value greater than 10. For example: 2 | 20 1 | 11 2 | 8 1 | 16 2 |3 Would return MODE(2,1,1) = 1. How do I pass the MODE() function only the criteria-filtered cells? Thanks! |
#15
|
|||
|
|||
Calculating the mode of a criteria-based range
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Kathy L." wrote in message ... I think I finally have this working. Thank you VERY much!!!! "T. Valko" wrote: Try something like this (array entered): =MODE(IF(('Raw Data'!A1:A10=Criteria!A2)*('Raw Data'!B1:B10=Criteria!B2)*('Raw Data'!C1:C10=Criteria!C2),'Raw Data'!D110)) Note that if there is no mode (at least 2 instances) that meets all the criteria the formula returns an error. -- Biff Microsoft Excel MVP "Kathy L." wrote in message ... I have one tab of raw data (several columns), which include 'Company', 'Business Group', 'Job Type' and 'Source Code'. Another tab is the control criteria, which include 'Company', 'Business Group' and 'Job Type'. The row below the criteria header includes a specific company, business group, and job type that match values in the raw data. Here is a sampling: Raw data: A B C D 1 Company Business Group Job Type Source Type Code 2 AFFILIATES CARD BUSINESS Marketing 12 3 CONSUMER SALES Retail 7 4 AFFILIATES CARD BUSINESS Marketing 7 5 FACILITIES FACILITIES Technician 5 6 AFFILIATES CARD BUSINESS Marketing 6 7 AFFILIATES CARD BUSINESS Marketing 6 Criteria page: A B C 1 Company Business Group Job Type 2 AFFILIATES CARD BUSINESS Marketing Note: The values for Company and Bus. Group criteria point to another cell with a different formula. The intent is a user will select a company, etc. and the data will populate for that company. Based on the criteria, I'd like to identify the source appearing most often. Result = 6 "T. Valko" wrote: How about providing some details? -- Biff Microsoft Excel MVP "Kathy L." wrote in message news Thank you. I wish there were more Dxxx functions! My problem (I think) is how do I reference the criteria range in the IF statement's Logical Test, if I work with your array function below? I'm having difficulty translating the array function below to my criteria range and raw data. "T. Valko" wrote: There is no "DMODE" function. If you want to use the MODE() function and reference the criteria range that's used by other D functions the criteria would have to be values only and not contain any operators. (although you could probably remove any operators within the formula but that just adds [undue] complexity. -- Biff Microsoft Excel MVP "Kathy L." Kathy wrote in message ... Hello, I have found this help useful, however I'm not quite getting this to work as expected. I can get this to work with the example below, however my file is using database controls used in other DCOUNT functions. For the criteria, I'd like to point it to the controls, which are based on a larger set of raw data. Perhaps it would be easier to reference my spreadsheet if I send to you? Please let me know if I can email you. Thank you very much. "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... That did exactly what I needed. Thanks a lot! "T. Valko" wrote: Try this array formula** : =MODE(IF(B1:B510,A1:A5)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... I have two columns of data. I want to calculate the mode of some cells in the first column - including only those cells whose corresponding cell in the second column has a value greater than 10. For example: 2 | 20 1 | 11 2 | 8 1 | 16 2 |3 Would return MODE(2,1,1) = 1. How do I pass the MODE() function only the criteria-filtered cells? Thanks! |
#16
|
|||
|
|||
Calculating the mode of a criteria-based range
I'm afraid I've uncovered another question...in my example below, suppose
Column A (Company) or Column B (Business Group) doesn't have a value? In the work I'm doing, a user will select a Company and if needed a Business Group. If a Company isn't selected, for example, I would want it to display the Mode for the selected Job Type for all Companies & Business Groups. Is this possible? Thank you. "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Kathy L." wrote in message ... I think I finally have this working. Thank you VERY much!!!! "T. Valko" wrote: Try something like this (array entered): =MODE(IF(('Raw Data'!A1:A10=Criteria!A2)*('Raw Data'!B1:B10=Criteria!B2)*('Raw Data'!C1:C10=Criteria!C2),'Raw Data'!D110)) Note that if there is no mode (at least 2 instances) that meets all the criteria the formula returns an error. -- Biff Microsoft Excel MVP "Kathy L." wrote in message ... I have one tab of raw data (several columns), which include 'Company', 'Business Group', 'Job Type' and 'Source Code'. Another tab is the control criteria, which include 'Company', 'Business Group' and 'Job Type'. The row below the criteria header includes a specific company, business group, and job type that match values in the raw data. Here is a sampling: Raw data: A B C D 1 Company Business Group Job Type Source Type Code 2 AFFILIATES CARD BUSINESS Marketing 12 3 CONSUMER SALES Retail 7 4 AFFILIATES CARD BUSINESS Marketing 7 5 FACILITIES FACILITIES Technician 5 6 AFFILIATES CARD BUSINESS Marketing 6 7 AFFILIATES CARD BUSINESS Marketing 6 Criteria page: A B C 1 Company Business Group Job Type 2 AFFILIATES CARD BUSINESS Marketing Note: The values for Company and Bus. Group criteria point to another cell with a different formula. The intent is a user will select a company, etc. and the data will populate for that company. Based on the criteria, I'd like to identify the source appearing most often. Result = 6 "T. Valko" wrote: How about providing some details? -- Biff Microsoft Excel MVP "Kathy L." wrote in message news Thank you. I wish there were more Dxxx functions! My problem (I think) is how do I reference the criteria range in the IF statement's Logical Test, if I work with your array function below? I'm having difficulty translating the array function below to my criteria range and raw data. "T. Valko" wrote: There is no "DMODE" function. If you want to use the MODE() function and reference the criteria range that's used by other D functions the criteria would have to be values only and not contain any operators. (although you could probably remove any operators within the formula but that just adds [undue] complexity. -- Biff Microsoft Excel MVP "Kathy L." Kathy wrote in message ... Hello, I have found this help useful, however I'm not quite getting this to work as expected. I can get this to work with the example below, however my file is using database controls used in other DCOUNT functions. For the criteria, I'd like to point it to the controls, which are based on a larger set of raw data. Perhaps it would be easier to reference my spreadsheet if I send to you? Please let me know if I can email you. Thank you very much. "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... That did exactly what I needed. Thanks a lot! "T. Valko" wrote: Try this array formula** : =MODE(IF(B1:B510,A1:A5)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... I have two columns of data. I want to calculate the mode of some cells in the first column - including only those cells whose corresponding cell in the second column has a value greater than 10. For example: 2 | 20 1 | 11 2 | 8 1 | 16 2 |3 Would return MODE(2,1,1) = 1. How do I pass the MODE() function only the criteria-filtered cells? Thanks! |
#17
|
|||
|
|||
Calculating the mode of a criteria-based range
In addition (the work I'm trying to do is getting quite complex), I found
your solution under the subject "Second Most Common Value" very useful using the following formulas: MODE(F2:F8) MODE(IF(COUNTIF(F$11:F11,F$2:F$8)=0,F$2:F$8+{0,0}) ) MODE(IF(COUNTIF(F$11:F12,F$2:F$8)=0,F$2:F$8+{0,0}) ) If I wanted to get the 2nd, 3rd, etc. most common value using the solutions you provided below with the criterias, would this be possible? How would I nest the formulas? Thank you very much. "Kathy L." wrote: I'm afraid I've uncovered another question...in my example below, suppose Column A (Company) or Column B (Business Group) doesn't have a value? In the work I'm doing, a user will select a Company and if needed a Business Group. If a Company isn't selected, for example, I would want it to display the Mode for the selected Job Type for all Companies & Business Groups. Is this possible? Thank you. "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Kathy L." wrote in message ... I think I finally have this working. Thank you VERY much!!!! "T. Valko" wrote: Try something like this (array entered): =MODE(IF(('Raw Data'!A1:A10=Criteria!A2)*('Raw Data'!B1:B10=Criteria!B2)*('Raw Data'!C1:C10=Criteria!C2),'Raw Data'!D110)) Note that if there is no mode (at least 2 instances) that meets all the criteria the formula returns an error. -- Biff Microsoft Excel MVP "Kathy L." wrote in message ... I have one tab of raw data (several columns), which include 'Company', 'Business Group', 'Job Type' and 'Source Code'. Another tab is the control criteria, which include 'Company', 'Business Group' and 'Job Type'. The row below the criteria header includes a specific company, business group, and job type that match values in the raw data. Here is a sampling: Raw data: A B C D 1 Company Business Group Job Type Source Type Code 2 AFFILIATES CARD BUSINESS Marketing 12 3 CONSUMER SALES Retail 7 4 AFFILIATES CARD BUSINESS Marketing 7 5 FACILITIES FACILITIES Technician 5 6 AFFILIATES CARD BUSINESS Marketing 6 7 AFFILIATES CARD BUSINESS Marketing 6 Criteria page: A B C 1 Company Business Group Job Type 2 AFFILIATES CARD BUSINESS Marketing Note: The values for Company and Bus. Group criteria point to another cell with a different formula. The intent is a user will select a company, etc. and the data will populate for that company. Based on the criteria, I'd like to identify the source appearing most often. Result = 6 "T. Valko" wrote: How about providing some details? -- Biff Microsoft Excel MVP "Kathy L." wrote in message news Thank you. I wish there were more Dxxx functions! My problem (I think) is how do I reference the criteria range in the IF statement's Logical Test, if I work with your array function below? I'm having difficulty translating the array function below to my criteria range and raw data. "T. Valko" wrote: There is no "DMODE" function. If you want to use the MODE() function and reference the criteria range that's used by other D functions the criteria would have to be values only and not contain any operators. (although you could probably remove any operators within the formula but that just adds [undue] complexity. -- Biff Microsoft Excel MVP "Kathy L." Kathy wrote in message ... Hello, I have found this help useful, however I'm not quite getting this to work as expected. I can get this to work with the example below, however my file is using database controls used in other DCOUNT functions. For the criteria, I'd like to point it to the controls, which are based on a larger set of raw data. Perhaps it would be easier to reference my spreadsheet if I send to you? Please let me know if I can email you. Thank you very much. "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... That did exactly what I needed. Thanks a lot! "T. Valko" wrote: Try this array formula** : =MODE(IF(B1:B510,A1:A5)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... I have two columns of data. I want to calculate the mode of some cells in the first column - including only those cells whose corresponding cell in the second column has a value greater than 10. For example: 2 | 20 1 | 11 2 | 8 1 | 16 2 |3 Would return MODE(2,1,1) = 1. How do I pass the MODE() function only the criteria-filtered cells? Thanks! |
#18
|
|||
|
|||
Calculating the mode of a criteria-based range
I'm not sure I understand. See if this is what you had in mind.
Criteria for the mode: A1 = company name or EMPTY B1 = business group C1 = job type Comp = named range for the companies BG = named range for the business groups Type = named range for the job types X = named range for the numeric values where we get the mode Array entered** : =MODE(IF(IF(A1"",(Comp=A1)*(BG=B1)*(Type=C1),(BG =B1)*(Type=C1)),X)) What it means: if A1 is EMPTY (no company selected) returns the mode for only the business group and the job type. If A1 is not EMPTY (a company has been selected) returns the mode for the company, business group and job type. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Kathy L." wrote in message ... I'm afraid I've uncovered another question...in my example below, suppose Column A (Company) or Column B (Business Group) doesn't have a value? In the work I'm doing, a user will select a Company and if needed a Business Group. If a Company isn't selected, for example, I would want it to display the Mode for the selected Job Type for all Companies & Business Groups. Is this possible? Thank you. "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Kathy L." wrote in message ... I think I finally have this working. Thank you VERY much!!!! "T. Valko" wrote: Try something like this (array entered): =MODE(IF(('Raw Data'!A1:A10=Criteria!A2)*('Raw Data'!B1:B10=Criteria!B2)*('Raw Data'!C1:C10=Criteria!C2),'Raw Data'!D110)) Note that if there is no mode (at least 2 instances) that meets all the criteria the formula returns an error. -- Biff Microsoft Excel MVP "Kathy L." wrote in message ... I have one tab of raw data (several columns), which include 'Company', 'Business Group', 'Job Type' and 'Source Code'. Another tab is the control criteria, which include 'Company', 'Business Group' and 'Job Type'. The row below the criteria header includes a specific company, business group, and job type that match values in the raw data. Here is a sampling: Raw data: A B C D 1 Company Business Group Job Type Source Type Code 2 AFFILIATES CARD BUSINESS Marketing 12 3 CONSUMER SALES Retail 7 4 AFFILIATES CARD BUSINESS Marketing 7 5 FACILITIES FACILITIES Technician 5 6 AFFILIATES CARD BUSINESS Marketing 6 7 AFFILIATES CARD BUSINESS Marketing 6 Criteria page: A B C 1 Company Business Group Job Type 2 AFFILIATES CARD BUSINESS Marketing Note: The values for Company and Bus. Group criteria point to another cell with a different formula. The intent is a user will select a company, etc. and the data will populate for that company. Based on the criteria, I'd like to identify the source appearing most often. Result = 6 "T. Valko" wrote: How about providing some details? -- Biff Microsoft Excel MVP "Kathy L." wrote in message news Thank you. I wish there were more Dxxx functions! My problem (I think) is how do I reference the criteria range in the IF statement's Logical Test, if I work with your array function below? I'm having difficulty translating the array function below to my criteria range and raw data. "T. Valko" wrote: There is no "DMODE" function. If you want to use the MODE() function and reference the criteria range that's used by other D functions the criteria would have to be values only and not contain any operators. (although you could probably remove any operators within the formula but that just adds [undue] complexity. -- Biff Microsoft Excel MVP "Kathy L." Kathy wrote in message ... Hello, I have found this help useful, however I'm not quite getting this to work as expected. I can get this to work with the example below, however my file is using database controls used in other DCOUNT functions. For the criteria, I'd like to point it to the controls, which are based on a larger set of raw data. Perhaps it would be easier to reference my spreadsheet if I send to you? Please let me know if I can email you. Thank you very much. "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... That did exactly what I needed. Thanks a lot! "T. Valko" wrote: Try this array formula** : =MODE(IF(B1:B510,A1:A5)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... I have two columns of data. I want to calculate the mode of some cells in the first column - including only those cells whose corresponding cell in the second column has a value greater than 10. For example: 2 | 20 1 | 11 2 | 8 1 | 16 2 |3 Would return MODE(2,1,1) = 1. How do I pass the MODE() function only the criteria-filtered cells? Thanks! |
#19
|
|||
|
|||
Calculating the mode of a criteria-based range
the work I'm trying to do is getting quite complex
If I wanted to get the 2nd, 3rd, etc. most common value with the criterias, would this be possible? Let me see what I can come up with. Probably won't get to it until Friday PM. -- Biff Microsoft Excel MVP "Kathy L." wrote in message news In addition (the work I'm trying to do is getting quite complex), I found your solution under the subject "Second Most Common Value" very useful using the following formulas: MODE(F2:F8) MODE(IF(COUNTIF(F$11:F11,F$2:F$8)=0,F$2:F$8+{0,0}) ) MODE(IF(COUNTIF(F$11:F12,F$2:F$8)=0,F$2:F$8+{0,0}) ) If I wanted to get the 2nd, 3rd, etc. most common value using the solutions you provided below with the criterias, would this be possible? How would I nest the formulas? Thank you very much. "Kathy L." wrote: I'm afraid I've uncovered another question...in my example below, suppose Column A (Company) or Column B (Business Group) doesn't have a value? In the work I'm doing, a user will select a Company and if needed a Business Group. If a Company isn't selected, for example, I would want it to display the Mode for the selected Job Type for all Companies & Business Groups. Is this possible? Thank you. "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Kathy L." wrote in message ... I think I finally have this working. Thank you VERY much!!!! "T. Valko" wrote: Try something like this (array entered): =MODE(IF(('Raw Data'!A1:A10=Criteria!A2)*('Raw Data'!B1:B10=Criteria!B2)*('Raw Data'!C1:C10=Criteria!C2),'Raw Data'!D110)) Note that if there is no mode (at least 2 instances) that meets all the criteria the formula returns an error. -- Biff Microsoft Excel MVP "Kathy L." wrote in message ... I have one tab of raw data (several columns), which include 'Company', 'Business Group', 'Job Type' and 'Source Code'. Another tab is the control criteria, which include 'Company', 'Business Group' and 'Job Type'. The row below the criteria header includes a specific company, business group, and job type that match values in the raw data. Here is a sampling: Raw data: A B C D 1 Company Business Group Job Type Source Type Code 2 AFFILIATES CARD BUSINESS Marketing 12 3 CONSUMER SALES Retail 7 4 AFFILIATES CARD BUSINESS Marketing 7 5 FACILITIES FACILITIES Technician 5 6 AFFILIATES CARD BUSINESS Marketing 6 7 AFFILIATES CARD BUSINESS Marketing 6 Criteria page: A B C 1 Company Business Group Job Type 2 AFFILIATES CARD BUSINESS Marketing Note: The values for Company and Bus. Group criteria point to another cell with a different formula. The intent is a user will select a company, etc. and the data will populate for that company. Based on the criteria, I'd like to identify the source appearing most often. Result = 6 "T. Valko" wrote: How about providing some details? -- Biff Microsoft Excel MVP "Kathy L." wrote in message news Thank you. I wish there were more Dxxx functions! My problem (I think) is how do I reference the criteria range in the IF statement's Logical Test, if I work with your array function below? I'm having difficulty translating the array function below to my criteria range and raw data. "T. Valko" wrote: There is no "DMODE" function. If you want to use the MODE() function and reference the criteria range that's used by other D functions the criteria would have to be values only and not contain any operators. (although you could probably remove any operators within the formula but that just adds [undue] complexity. -- Biff Microsoft Excel MVP "Kathy L." Kathy wrote in message ... Hello, I have found this help useful, however I'm not quite getting this to work as expected. I can get this to work with the example below, however my file is using database controls used in other DCOUNT functions. For the criteria, I'd like to point it to the controls, which are based on a larger set of raw data. Perhaps it would be easier to reference my spreadsheet if I send to you? Please let me know if I can email you. Thank you very much. "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... That did exactly what I needed. Thanks a lot! "T. Valko" wrote: Try this array formula** : =MODE(IF(B1:B510,A1:A5)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... I have two columns of data. I want to calculate the mode of some cells in the first column - including only those cells whose corresponding cell in the second column has a value greater than 10. For example: 2 | 20 1 | 11 2 | 8 1 | 16 2 |3 Would return MODE(2,1,1) = 1. How do I pass the MODE() function only the criteria-filtered cells? Thanks! |
#20
|
|||
|
|||
Calculating the mode of a criteria-based range
I'm sorry I'm just now getting a chance to reply to your posts. I think I
have found an alternate solution for what I'm trying to solve, since the functions are getting pretty complicated. Thank you so much for your help, and I will post again if I'm still stuck and need assistance with this function again. "T. Valko" wrote: I'm not sure I understand. See if this is what you had in mind. Criteria for the mode: A1 = company name or EMPTY B1 = business group C1 = job type Comp = named range for the companies BG = named range for the business groups Type = named range for the job types X = named range for the numeric values where we get the mode Array entered** : =MODE(IF(IF(A1"",(Comp=A1)*(BG=B1)*(Type=C1),(BG =B1)*(Type=C1)),X)) What it means: if A1 is EMPTY (no company selected) returns the mode for only the business group and the job type. If A1 is not EMPTY (a company has been selected) returns the mode for the company, business group and job type. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Kathy L." wrote in message ... I'm afraid I've uncovered another question...in my example below, suppose Column A (Company) or Column B (Business Group) doesn't have a value? In the work I'm doing, a user will select a Company and if needed a Business Group. If a Company isn't selected, for example, I would want it to display the Mode for the selected Job Type for all Companies & Business Groups. Is this possible? Thank you. "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Kathy L." wrote in message ... I think I finally have this working. Thank you VERY much!!!! "T. Valko" wrote: Try something like this (array entered): =MODE(IF(('Raw Data'!A1:A10=Criteria!A2)*('Raw Data'!B1:B10=Criteria!B2)*('Raw Data'!C1:C10=Criteria!C2),'Raw Data'!D110)) Note that if there is no mode (at least 2 instances) that meets all the criteria the formula returns an error. -- Biff Microsoft Excel MVP "Kathy L." wrote in message ... I have one tab of raw data (several columns), which include 'Company', 'Business Group', 'Job Type' and 'Source Code'. Another tab is the control criteria, which include 'Company', 'Business Group' and 'Job Type'. The row below the criteria header includes a specific company, business group, and job type that match values in the raw data. Here is a sampling: Raw data: A B C D 1 Company Business Group Job Type Source Type Code 2 AFFILIATES CARD BUSINESS Marketing 12 3 CONSUMER SALES Retail 7 4 AFFILIATES CARD BUSINESS Marketing 7 5 FACILITIES FACILITIES Technician 5 6 AFFILIATES CARD BUSINESS Marketing 6 7 AFFILIATES CARD BUSINESS Marketing 6 Criteria page: A B C 1 Company Business Group Job Type 2 AFFILIATES CARD BUSINESS Marketing Note: The values for Company and Bus. Group criteria point to another cell with a different formula. The intent is a user will select a company, etc. and the data will populate for that company. Based on the criteria, I'd like to identify the source appearing most often. Result = 6 "T. Valko" wrote: How about providing some details? -- Biff Microsoft Excel MVP "Kathy L." wrote in message news Thank you. I wish there were more Dxxx functions! My problem (I think) is how do I reference the criteria range in the IF statement's Logical Test, if I work with your array function below? I'm having difficulty translating the array function below to my criteria range and raw data. "T. Valko" wrote: There is no "DMODE" function. If you want to use the MODE() function and reference the criteria range that's used by other D functions the criteria would have to be values only and not contain any operators. (although you could probably remove any operators within the formula but that just adds [undue] complexity. -- Biff Microsoft Excel MVP "Kathy L." Kathy wrote in message ... Hello, I have found this help useful, however I'm not quite getting this to work as expected. I can get this to work with the example below, however my file is using database controls used in other DCOUNT functions. For the criteria, I'd like to point it to the controls, which are based on a larger set of raw data. Perhaps it would be easier to reference my spreadsheet if I send to you? Please let me know if I can email you. Thank you very much. "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... That did exactly what I needed. Thanks a lot! "T. Valko" wrote: Try this array formula** : =MODE(IF(B1:B510,A1:A5)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... I have two columns of data. I want to calculate the mode of some cells in the first column - including only those cells whose corresponding cell in the second column has a value greater than 10. For example: 2 | 20 1 | 11 2 | 8 1 | 16 2 |3 Would return MODE(2,1,1) = 1. How do I pass the MODE() function only the criteria-filtered cells? Thanks! |
Thread Tools | |
Display Modes | |
|
|