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 |
#1
|
|||
|
|||
Calculating the mode of a criteria-based range
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! |
#2
|
|||
|
|||
Calculating the mode of a criteria-based range
Use AutoFilter:
Say A1 thru B6 contain: V CRT 2 20 1 11 2 8 1 16 2 3 Click on B1 and: Data Filter Autofilter Custom is greater than 10 this will produce: V CRT 2 20 1 11 1 16 Copy and paste this to, say H16 and then =MODE(H16:H100) will get you what you want. -- Gary''s Student - gsnu200772 "PaladinWhite" wrote: 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! |
#3
|
|||
|
|||
Calculating the mode of a criteria-based range
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! |
#4
|
|||
|
|||
Calculating the mode of a criteria-based range
Unfortunately, rows are being added to the sheet all the time - I'd rather
not have to filter, copy & paste every time new ones are added. Isn't there a way to do it so that I can keep a running mode? "Gary''s Student" wrote: Use AutoFilter: Say A1 thru B6 contain: V CRT 2 20 1 11 2 8 1 16 2 3 Click on B1 and: Data Filter Autofilter Custom is greater than 10 this will produce: V CRT 2 20 1 11 1 16 Copy and paste this to, say H16 and then =MODE(H16:H100) will get you what you want. -- Gary''s Student - gsnu200772 "PaladinWhite" wrote: 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! |
#5
|
|||
|
|||
Calculating the mode of a criteria-based range
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! |
#6
|
|||
|
|||
Calculating the mode of a criteria-based range
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! |
#7
|
|||
|
|||
Calculating the mode of a criteria-based range
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! |
#8
|
|||
|
|||
Calculating the mode of a criteria-based range
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! |
#9
|
|||
|
|||
Calculating the mode of a criteria-based range
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! |
#10
|
|||
|
|||
Calculating the mode of a criteria-based range
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 | |
|
|