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

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


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

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)

"Sam via OfficeKB.com" wrote:


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


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

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


"Salesmaster" wrote in message
...
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)

"Sam via OfficeKB.com" wrote:


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


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

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!

In article ,
"Sam via OfficeKB.com" wrote:

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

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.

Domenic wrote:
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


--
Message posted via http://www.officekb.com

On Sat, 16 Jul 2005 23:49:29 GMT, "Sam via OfficeKB.com"
wrote:
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

--
"Learning is a behavior that results from consequences."
B.F. Skinner

Hi Magnus,

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

Suggestions welcome.

Thanks
Sam

Mangus Pyke wrote:
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

--
"Learning is a behavior that results from consequences."
B.F. Skinner


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

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?

In article ,
"Sam via OfficeKB.com" wrote:

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.

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 wrote:
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?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

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?

In article ,
"Sam via OfficeKB.com" wrote:

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

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.

Sam via OfficeKB.com wrote:
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.

Domenic wrote:

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



--

[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.

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

Domenic wrote:
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


--
Message posted via http://www.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

Aladin Akyurek wrote:
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.


--
Message posted via http://www.officekb.com

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","Hea der3"},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!

In article ,
"Sam via OfficeKB.com" wrote:

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

Domenic wrote:
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.

In article ,
Aladin Akyurek wrote:

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...

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

Domenic wrote:
In article ,
Aladin Akyurek wrote:


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...

--

[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.

That's great! Thanks Aladin!

In article ,
Aladin Akyurek wrote:

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

Domenic wrote:
In article ,
Aladin Akyurek wrote:


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...

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

Domenic wrote:
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","He ader3"},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(A 2: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


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

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)

Aladin Akyurek wrote:
It always adds to efficieny. And the idiom still stays Longre's.

It's not unsafe to reduce

[quoted text clipped - 9 lines]
increase in efficiency negligible, though? I tend to prefer Longre's
idiom...

Thanks
Sam


--
Message posted via http://www.officekb.com