Arithmetical Mode Value for Filtered cells in Multiple Non-Adjacent columns

[Sam via OfficeKB.com]

Hi All,

Can you advise how I can get the overall MODE value for 5 non-adjacent
numerical columns that have been filtered on various criteria. I require the
MODE value for ONLY the Visible Filtered cells that remain - can this be made
available without specifying numerous criteria in the Formula?


The Formula below was provided by Domenic to calcuate the MODE value of a
single TEXT criterion that is located in several (5) non-adjacent columns to
show the MODE of ONLY Visible Filtered cells.

=MODE(IF((SUBTOTAL(3,OFFSET(B1:J10,ROW(B1:J10)-MIN(ROW(B1:J10)),0,1))>0)*
(B1:J10="North"),A1:I10))
....confirmed with CONTROL+SHIFT+ENTER.


Thanks
Sam

 

[Salesmaster]

Hello,

First time excel user trying to set up a worksheet.I need to get answer to
this question in my worksheet.If (cell a) is < 12% ( cell b) how much more
(cell a) is needed to be at min 12% (cell b)

 

[Anne Troy]

Hi, Salesmaster. You should ask your question by itself instead of inside
someone elses.
=if(a1<.12,.12-a1,a1)
*******************
~Anne Troy

www.OfficeArticles.com

 

[Domenic]

To calculate MODE for all visible filtered cells, try...

=MODE(IF(SUBTOTAL(3,OFFSET(A1:J10,ROW(A1:J10)-MIN(ROW(A1:J10)),0,1)),A1:J
10))

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

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thanks for response.

I'm not getting the correct value - I think it's because the Formula range (A-
J) encounters text values within the non-adjacent range; i.e assuming that
Columns B, D, F, H, and J are the five non-adjacent numerical columns, of
which I require the MODE value - columns A, C, E, G and I with text values
are being captured within the Formula below and may be distorting the result.

To calculate MODE for all visible filtered cells, try...

=MODE(IF(SUBTOTAL(3,OFFSET(A1:J10,ROW(A1:J10)-MIN(ROW(A1:J10)),0,1)),A1:J
10))
...confirmed with CONTROL+SHIFT+ENTER.

Thanks
Sam

 

[Mangus Pyke]

I'm not getting the correct value - I think it's because the Formula range (A-
J) encounters text values within the non-adjacent range; i.e assuming that
Columns B, D, F, H, and J are the five non-adjacent numerical columns, of
which I require the MODE value - columns A, C, E, G and I with text values
are being captured within the Formula below and may be distorting the result.

Please do not take offense to this, but given that this is an array,
did you enter it using Control+Shift+Enter?

MP

 

[Sam via OfficeKB.com]

Hi Magnus,

I did enter the Formula using Control+Shift+Enter.

Suggestions welcome.

Thanks
Sam

 

[Domenic]

Actually, MODE ignores text values, so that shouldn't be an issue. Does
your table contain blank cells? If so, try the following formula
instead...

=MODE(IF(SUBTOTAL(3,OFFSET(A1:J10,ROW(A1:J10)-MIN(ROW(A1:J10)),0,1))*(A1:
J10<>""),A1:J10))

....confirmed with CONTROL+SHIFT+ENTER. Does this help?

 

[Sam via OfficeKB.com]

Hi Domenic,

Yes, there are some blank cells at the end. I've tried the Formula below but
I now get a much lower value than I should.

Thanks
Sam

 

[Domenic]

Make sure that your numbers are actually numerical values. Try the
following...

1) Select an empty cell

2) Edit > Copy

3) Select your range of numbers

4) Edit > Paste Special > Add > Ok

Does this help?

 

[Aladin Akyurek]

Assuming that row 1 of A:J houses header values...

=MODE(IF(SUBTOTAL(3,OFFSET(A2:J10,ROW(A2:J10)-ROW(A2),0,1)),IF((MOD(COLUMN(A2:J10)-COLUMN(A2)+1,2)=0)*ISNUMBER(A2:J10),A2:J10)))

which must be confirmed with control+shift+enter.

Hi Domenic,

Thanks for response.

I'm not getting the correct value - I think it's because the Formula range (A-
J) encounters text values within the non-adjacent range; i.e assuming that
Columns B, D, F, H, and J are the five non-adjacent numerical columns, of
which I require the MODE value - columns A, C, E, G and I with text values
are being captured within the Formula below and may be distorting the result.




Thanks
Sam

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

 

[Sam via OfficeKB.com]

Hi Domenic,

Tested numbers and they are numerical.

Apologies for this ongoing Post: I've just realised my error in that I'm not
getting the correct MODE value because I've included columns with numerical
values that need to be excluded when calculating the MODE value. The columns
that I need the overall calculated MODE value for are columns F, N, V, AD and
AL using the feature of Filtered Visible cells.

My columns with data start from column A - AM and comprise the following:

Numerical data TO BE INCLUDED in MODE calculation = Columns F, N, V, AD, AL

Numerical data EXCLUDED in Mode calculation = Columns A, B, C, D, E, I, J, K,
L, M, Q, R, S, T, U, Y, Z, AA, AB, AC, AG, AH, AI, AJ, AK

Text Data = Columns G,O, W, AE, AM

Empty Columns = Columns H, P, X, AF, AN

I apologise for my confusion.

Thanks
Sam

Make sure that your numbers are actually numerical values. Try the
following...

1) Select an empty cell

2) Edit > Copy

3) Select your range of numbers

4) Edit > Paste Special > Add > Ok

Does this help?

Hi Domenic,

[quoted text clipped - 3 lines]

Thanks
Sam

 

[Sam via OfficeKB.com]

Hi Aladin,

Thank you for reply. I made a hash of my original explanation and detail my
requirements below:

Apologies for this ongoing Post: I've just realised my error in that I'm not
getting the correct MODE value because I've included columns with numerical
values that need to be excluded when calculating the MODE value. The columns
that I need the overall calculated MODE value for are columns F, N, V, AD and
AL using the feature of Filtered Visible cells.

My columns with data start from column A - AM and comprise the following:

Numerical data TO BE INCLUDED in MODE calculation = Columns F, N, V, AD, AL

Numerical data EXCLUDED in Mode calculation = Columns A, B, C, D, E, I, J, K,
L, M, Q, R, S, T, U, Y, Z, AA, AB, AC, AG, AH, AI, AJ, AK

Text Data = Columns G,O, W, AE, AM

Empty Columns = Columns H, P, X, AF, AN

I apologise for my confusion.

Thanks
Sam

 

[Domenic]

Hi Sam!

Let's adopt Aladin's formula...

=MODE(IF(SUBTOTAL(3,OFFSET(F2:AL10,ROW(F2:AL10)-MIN(ROW(F2:AL10)),0,1)),I
F((MOD(COLUMN(F2:AL10)-COLUMN(F2)+0,8)=0)*(ISNUMBER(F2:AL10)),F2:AL10)))

....confirmed with CONTROL+SHIFT+ENTER. An alternative would be to use
your column headers/labels to decide which columns you want to include
in the evaluation...

=MODE(IF(SUBTOTAL(3,OFFSET(A2:AM10,ROW(A2:AM10)-MIN(ROW(A2:AM10)),0,1)),I
F((ISNUMBER(MATCH(A1:AM1,{"Header1","Header2","Header3"},0)))*(ISNUMBER(A
2:AM10)),A2:AM10)))

....where Header1, Header2, and Header3 represent the column heading for
the columns you want included in the evaluation. Replace these with
your actual column headings and add to them as needed.

or

=MODE(IF(SUBTOTAL(3,OFFSET(A2:AM10,ROW(A2:AM10)-MIN(ROW(A2:AM10)),0,1)),I
F((ISNUMBER(MATCH(A1:AM1,A15:A17,0)))*(ISNUMBER(A2:AM10)),A2:AM10)))

....where A15:A17 contains a list of column headers indicating the
columns you want included in the evaluation.

Hope this helps!

 

[Aladin Akyurek]

Hi Sam!

Let's adopt Aladin's formula...

=MODE(IF(SUBTOTAL(3,OFFSET(F2:AL10,ROW(F2:AL10)-MIN(ROW(F2:AL10)),0,1)),I
F((MOD(COLUMN(F2:AL10)-COLUMN(F2)+0,8)=0)*(ISNUMBER(F2:AL10)),F2:AL10)))

...confirmed with CONTROL+SHIFT+ENTER.

[...]

It's not unsafe to reduce

-MIN(ROW(F2:AL10))

to just

-ROW(F2)

though.

 

[Domenic]

It's not unsafe to reduce

-MIN(ROW(F2:AL10))

to just

-ROW(F2)

though.

Thanks Aladin! I guess it would be a little more efficient. Is the
increase in efficiency negligible, though? I tend to prefer Longre's
idiom...

 

[Aladin Akyurek]

It always adds to efficieny. And the idiom still stays Longre's.

Thanks Aladin! I guess it would be a little more efficient. Is the
increase in efficiency negligible, though? I tend to prefer Longre's
idiom...

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

 

[Domenic]

That's great! Thanks Aladin!

It always adds to efficieny. And the idiom still stays Longre's.

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you for all your help and perseverance - greatly appreciated.

I've chosen this version of the Formula - works great:

=MODE(IF(SUBTOTAL(3,OFFSET(F2:AL10,ROW(F2:AL10)-MIN(ROW(F2:AL10)),0,1)),I
F((MOD(COLUMN(F2:AL10)-COLUMN(F2)+0,8)=0)*(ISNUMBER(F2:AL10)),F2:AL10)))
...confirmed with CONTROL+SHIFT+ENTER.

Apologies once again for not providing the complete picture previously.

Cheers
Sam

Hi Sam!

Let's adopt Aladin's formula...

=MODE(IF(SUBTOTAL(3,OFFSET(F2:AL10,ROW(F2:AL10)-MIN(ROW(F2:AL10)),0,1)),I
F((MOD(COLUMN(F2:AL10)-COLUMN(F2)+0,8)=0)*(ISNUMBER(F2:AL10)),F2:AL10)))

...confirmed with CONTROL+SHIFT+ENTER. An alternative would be to use
your column headers/labels to decide which columns you want to include
in the evaluation...

=MODE(IF(SUBTOTAL(3,OFFSET(A2:AM10,ROW(A2:AM10)-MIN(ROW(A2:AM10)),0,1)),I
F((ISNUMBER(MATCH(A1:AM1,{"Header1","Header2","Header3"},0)))*(ISNUMBER(A
2:AM10)),A2:AM10)))

...where Header1, Header2, and Header3 represent the column heading for
the columns you want included in the evaluation. Replace these with
your actual column headings and add to them as needed.

or

=MODE(IF(SUBTOTAL(3,OFFSET(A2:AM10,ROW(A2:AM10)-MIN(ROW(A2:AM10)),0,1)),I
F((ISNUMBER(MATCH(A1:AM1,A15:A17,0)))*(ISNUMBER(A2:AM10)),A2:AM10)))

...where A15:A17 contains a list of column headers indicating the
columns you want included in the evaluation.

Hope this helps!

Hi Domenic,

[quoted text clipped - 21 lines]

Thanks
Sam

 

[Sam via OfficeKB.com]

Hi Aladin,

Thank you for your added assistance with the Formula and helpful comments
regarding the Formula structure.

=MODE(IF(SUBTOTAL(3,OFFSET(F2:AL10,ROW(F2:AL10)-MIN(ROW(F2:AL10)),0,1)),I
F((MOD(COLUMN(F2:AL10)-COLUMN(F2)+0,8)=0)*(ISNUMBER(F2:AL10)),F2:AL10)))
....confirmed with CONTROL+SHIFT+ENTER.

Aladin Akyurek wrote:

It's not unsafe to reduce

-MIN(ROW(F2:AL10))

to just

-ROW(F2)

It always adds to efficieny. And the idiom still stays Longre's.
[quoted text clipped - 9 lines]

increase in efficiency negligible, though? I tend to prefer Longre's
idiom...

Thanks
Sam