Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mode
Hi,
I want to find out the most frequently occurring value for the below example. Based on the example below I want to know Column A = Sales, Column B = s682, which value is the most frequently occurred. Column A: Column B: Column C: Sales s682 4 Sales s681 1 Marketing s683 5 Sales s682 4 Finance s682 4 Sales s683 5 Sales s682 2 Thanks, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mode
With the data in the example, you may use the following array formula for the
column A data: =INDEX(A1:A7,MATCH(MAX(COUNTIF(A1:A7,A1:A7)),COUNT IF(A1:A7,A1:A7),0)) Enter it with CRTL+SHIFT+ENTER, and changes ranges as appropiate. Hope this helps, Miguel "Priscilla" wrote: Hi, I want to find out the most frequently occurring value for the below example. Based on the example below I want to know Column A = Sales, Column B = s682, which value is the most frequently occurred. Column A: Column B: Column C: Sales s682 4 Sales s681 1 Marketing s683 5 Sales s682 4 Finance s682 4 Sales s683 5 Sales s682 2 Thanks, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mode
Tools DataAnalysis Histogram follow the menus........
Vaya con Dios, Chuck, CABGx3 "Priscilla" wrote: Hi, I want to find out the most frequently occurring value for the below example. Based on the example below I want to know Column A = Sales, Column B = s682, which value is the most frequently occurred. Column A: Column B: Column C: Sales s682 4 Sales s681 1 Marketing s683 5 Sales s682 4 Finance s682 4 Sales s683 5 Sales s682 2 Thanks, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mode
You could do this rather easily with Pivot tables or by extracting the unique
values in each column and using Countif() formulas To calc with pivot tables, start by making sure each column has a text label as a header, then select one of the data cells and use Data-Pivot table... Click on the Finish button and in the new sheet that appears drag the column header for col A onto the Row Fields area, then drag the same column header into the Data area "Priscilla" wrote: Hi, I want to find out the most frequently occurring value for the below example. Based on the example below I want to know Column A = Sales, Column B = s682, which value is the most frequently occurred. Column A: Column B: Column C: Sales s682 4 Sales s681 1 Marketing s683 5 Sales s682 4 Finance s682 4 Sales s683 5 Sales s682 2 Thanks, |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mode
Clever!
"Miguel Zapico" wrote: With the data in the example, you may use the following array formula for the column A data: =INDEX(A1:A7,MATCH(MAX(COUNTIF(A1:A7,A1:A7)),COUNT IF(A1:A7,A1:A7),0)) Enter it with CRTL+SHIFT+ENTER, and changes ranges as appropiate. Hope this helps, Miguel "Priscilla" wrote: Hi, I want to find out the most frequently occurring value for the below example. Based on the example below I want to know Column A = Sales, Column B = s682, which value is the most frequently occurred. Column A: Column B: Column C: Sales s682 4 Sales s681 1 Marketing s683 5 Sales s682 4 Finance s682 4 Sales s683 5 Sales s682 2 Thanks, |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mode
If you're asking for the most frquently occurring number in column C when column A is "sales" and column B is s682, i.e. the answer is 4 in your example then you can use this formula =MODE(IF((A1:A10="sales")*(B1:B10="s682"),C1:C10)) confirmed with CTRL+SHIFT+ENTER adjust ranges as necessary -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=542198 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mode
I used the below formula, and got N/A as a result? Not sure where it went
wrong. =MODE(IF(('ES 0421'!$A$2:$A$295=A6)*('ES 0421'!$X$2:$X$295=B6),'ES 0421'!$Q$2:$Q$295)) "daddylonglegs" wrote: If you're asking for the most frquently occurring number in column C when column A is "sales" and column B is s682, i.e. the answer is 4 in your example then you can use this formula =MODE(IF((A1:A10="sales")*(B1:B10="s682"),C1:C10)) confirmed with CTRL+SHIFT+ENTER adjust ranges as necessary -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=542198 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mode
Do you have #N/A anywhere within your ranges? Either that or you haven't confirmed with CTRL+SHIFT+ENTER. -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=542198 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mode
No, I don't have #N/A anywhere within the ranges. I did confirmed with
CTRL+SHIFT+ENTER. "daddylonglegs" wrote: Do you have #N/A anywhere within your ranges? Either that or you haven't confirmed with CTRL+SHIFT+ENTER. -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=542198 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mode
What are you expecting the answer to be? Note that the values in Q2:Q295 need to be numeric Mode will give you #N/A if Q2:Q295 does not contain any duplicate numbers where the two conditions are satisfied for columns A and X, e.g. amending your original example: For this data Column A: Column B: Column C: Sales s682 3 Sales s681 1 Marketing s683 5 Sales s682 4 Finance s682 4 Sales s683 5 Sales s682 2 This formula =MODE(IF((A1:A10="sales")*(B1:B10="s682"),C1:C10)) confirmed with CTRL+SHIFT+ENTER will return #N/A because =MODE({3,4,2}) has no duplicate numbers -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=542198 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mode
Is there anyway I can get around that?
"daddylonglegs" wrote: What are you expecting the answer to be? Note that the values in Q2:Q295 need to be numeric Mode will give you #N/A if Q2:Q295 does not contain any duplicate numbers where the two conditions are satisfied for columns A and X, e.g. amending your original example: For this data Column A: Column B: Column C: Sales s682 3 Sales s681 1 Marketing s683 5 Sales s682 4 Finance s682 4 Sales s683 5 Sales s682 2 This formula =MODE(IF((A1:A10="sales")*(B1:B10="s682"),C1:C10)) confirmed with CTRL+SHIFT+ENTER will return #N/A because =MODE({3,4,2}) has no duplicate numbers -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=542198 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mode
You could make it show something else other than #N/A, e.g. =IF(ISNA(MODE(IF((A1:A10="sales")*(B1:B10="s682"), C1:C10))),"No duplicate values",MODE(IF((A1:A10="sales")*(B1:B10="s682"),C 1:C10))) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=542198 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Arithmetical Mode Value for Filtered cells in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
How do I display more than one mode in a cell? | Excel Worksheet Functions | |||
Mode Function with Criteria | Excel Worksheet Functions | |||
Combo Box goes to edit mode even if design mode is in OFF position | Excel Discussion (Misc queries) | |||
coverting answer from Radian mode to degree mode | Excel Worksheet Functions |