Arithmetical Mode Value for Filtered cells in Multiple Adjacent columns

  • Thread starter Sam via OfficeKB.com
  • Start date
S

Sam via OfficeKB.com

Hi All,

I require a Formula to produce the Arithmetical MODE for numerical values
taking into account the following:

Using Dynamic Named Range "Results" - spans 15 Columns and many Rows.

Dynamic Name "Results" is Defined as Refers To =OFFSET(History!$A$18,1,0,
COUNT(History!$A:$A),15)

Working with Auto-Filtered data so calculations need to display results for
Visible Filtered Cells.

Dynamic Range "Results" needs to be Offset one column to the right,
calculations will include columns 2-15.

Cells housing Formulas that return "" (blank) to be excluded from
calculations

Below is a link to a similar past Thread
http://www.officekb.com/Uwe/Forum.a...-cells-in-Multiple#[email protected]


Would appreciate a Formula that can incorporate the Dynamic Named Range
"Results".


Thanks
Sam
 
D

Domenic

Sam via OfficeKB.com said:
Dynamic Name "Results" is Defined as Refers To =OFFSET(History!$A$18,1,0,
COUNT(History!$A:$A),15)

Dynamic Range "Results" needs to be Offset one column to the right,
calculations will include columns 2-15.

Insert > Name > Define

Name: Results2

Refers to:

=OFFSET(Results,0,1,,14)
I require a Formula to produce the Arithmetical MODE for numerical values
taking into account the following:

Working with Auto-Filtered data so calculations need to display results for
Visible Filtered Cells.

Cells housing Formulas that return "" (blank) to be excluded from
calculations

Below is a link to a similar past Thread
http://www.officekb.com/Uwe/Forum.aspx/excel-functions/38962/Arithmetical-Mode
-Value-for-Filtered-cells-in-Multiple#5183599F654B4%40OfficeKB.com


Would appreciate a Formula that can incorporate the Dynamic Named Range
"Results".

=MODE(IF(SUBTOTAL(3,OFFSET(Results2,ROW(Results2)-MIN(ROW(Results2)),0,1)
),IF((MOD(COLUMN(Results2)-MIN(COLUMN(Results2))+0,8)=0)*(ISNUMBER(Result
s2)),Results2)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
S

Sam via OfficeKB.com

Hi Domenic,

Thank you very much. Your suggested Formula works great!

I just changed the MOD divisor to 1 instead of 8.
Name: Results2
Refers to:
=OFFSET(Results,0,1,,14)
=MODE(IF(SUBTOTAL(3,OFFSET(Results2,ROW(Results2)-MIN(ROW(Results2)),0,1)
),IF((MOD(COLUMN(Results2)-MIN(COLUMN(Results2))+0,8)=0)*(ISNUMBER(Result
s2)),Results2)))

...confirmed with CONTROL+SHIFT+ENTER
Cheers,
Sam
Dynamic Name "Results" is Defined as Refers To =OFFSET(History!$A$18,1,0,
COUNT(History!$A:$A),15)

Dynamic Range "Results" needs to be Offset one column to the right,
calculations will include columns 2-15.

Insert > Name > Define

Name: Results2

Refers to:

=OFFSET(Results,0,1,,14)
I require a Formula to produce the Arithmetical MODE for numerical values
taking into account the following:
[quoted text clipped - 11 lines]
Would appreciate a Formula that can incorporate the Dynamic Named Range
"Results".

=MODE(IF(SUBTOTAL(3,OFFSET(Results2,ROW(Results2)-MIN(ROW(Results2)),0,1)
),IF((MOD(COLUMN(Results2)-MIN(COLUMN(Results2))+0,8)=0)*(ISNUMBER(Result
s2)),Results2)))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
D

Domenic

Sam via OfficeKB.com said:
Thank you very much. Your suggested Formula works great!

You're very welcome!
I just changed the MOD divisor to 1 instead of 8.

In that case, you can eliminate the MOD argument...

=MODE(IF(SUBTOTAL(3,OFFSET(Results2,ROW(Results2)-MIN(ROW(Results2)),0,1)
),IF(ISNUMBER(Results2),Results2)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top