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