#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Priscilla
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Miguel Zapico
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Duke Carey
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Duke Carey
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
daddylonglegs
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Priscilla
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
daddylonglegs
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Priscilla
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
daddylonglegs
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Priscilla
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
daddylonglegs
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Arithmetical Mode Value for Filtered cells in Multiple Non-Adjacent columns Sam via OfficeKB.com Excel Worksheet Functions 19 July 18th 05 06:09 PM
How do I display more than one mode in a cell? Tyler Maciolek Excel Worksheet Functions 1 June 17th 05 04:01 AM
Mode Function with Criteria DaveShoe Excel Worksheet Functions 1 April 6th 05 06:22 PM
Combo Box goes to edit mode even if design mode is in OFF position Chas Excel Discussion (Misc queries) 0 January 7th 05 08:21 PM
coverting answer from Radian mode to degree mode Xmastrzman Excel Worksheet Functions 1 November 10th 04 05:45 PM


All times are GMT +1. The time now is 05:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"