Mode Function

[yank1221]

Dear All,

I am Quite new to use XL, and found some stuck with the funtion, maybe some body can help me for my problem.

I have 20 Data on Cell A4:A23 {21,19,22,21,29,32,21,21,20,21,19,23,22,21,23,49,21,16,19,20} I am using =MODE(A4:A23) I get (21) --> Frequent Value
The Problem is, I must Select All data in range A4:A23 Which not More Than 25,2 (21+20%) and not less than 16,8 (21-20%).
I must calculate and find the average of data in A4:A23
Within Criteria : not more than 25,2 adn not less than 16,8.

Total Of Data within Criteria Must Be : 334
and only 16 Data SO Average Become : 334/16 = 20,875
(29,32,49 & 16 not Calculate, out of criteria)
Did SOme body can help me to solve this problem?

Regards from Indonesia
Andreas H

 

[DonkeyOte]

=sumproduct(($a$4:$a$23>=16.8)*($a$4:$a$23<=25.2)*($a$4:$a$23))/sumproduct(($a$4:$a$23>=16.8)*($a$4:$a$23<=25.2))

 

[Richard Schollar]

Hi Andreas

<TABLE style="WIDTH: 235pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=313 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; WIDTH: 48pt; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17></TD><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; WIDTH: 48pt; BORDER-BOTTOM: #e9e9e9; BACKGROUND-COLOR: silver" width=64>A</TD><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; WIDTH: 91pt; BORDER-BOTTOM: #e9e9e9; BACKGROUND-COLOR: silver" width=121>B</TD><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; WIDTH: 48pt; BORDER-BOTTOM: #e9e9e9; BACKGROUND-COLOR: silver" width=64>C</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>4</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>21</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Upper boundary</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>25.2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>5</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>19</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Lower Boundary</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>16.8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>6</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>22</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Resultant Mode</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num x:fmla="=MODE(IF($B$2:$B$21>=$D3,IF($B$2:$B$21<=$D2,$B$2:$B$21)))">21</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>7</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>21</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>8</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>29</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Resultant Average</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="20.875" x:fmla="=AVERAGE(IF($B$2:$B$21>=$D3,IF($B$2:$B$21<=$D2,$B$2:$B$21)))" x:arrayrange="D6">20.875</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>9</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>32</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>10</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>21</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>11</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>21</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>12</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>20</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>13</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>21</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>14</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>19</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>15</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>23</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>16</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>22</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>17</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>21</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>18</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>23</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>19</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>49</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>20</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>21</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>21</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>16</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>22</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>19</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e9e9e9; BORDER-TOP: #e9e9e9; BORDER-LEFT: #e9e9e9; BORDER-BOTTOM: #e9e9e9; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>23</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>20</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>

Formula in C6 is:

=MODE(IF($A$4:$A$23>=$C5,IF($A$4:$A$23<=$C4,$A$4:$A$23)))

Formula in C8 is:

=AVERAGE(IF($A$4:$A$23>=$C5,IF($A$4:$A$23<=$C4,$A$4:$A$23)))

Both of these are array formula which must be confirmed with Ctrl+Shift+Enter. Following successful entry, Excel will surround with curly braces {} - do not try and enter these manually yourself.

 

[yank1221]

Dear Friend,

I just Trying to put formula as your sugestion, but THe result is #Value!

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>=MODE(IF($A$4:$A$23>=$C5;IF($A$4:$A$23<=$C4;$A$4:$A$23)))}</TD></TR></TBODY></TABLE>

There is some thing problem in writing the formula ?

 

[yank1221]

=sumproduct(($a$4:$a$23>=16.8)*($a$4:$a$23<=25.2)*($a$4:$a$23))/sumproduct(($a$4:$a$23>=16.8)*($a$4:$a$23<=25.2))

This Formula can work very well Thank YOu......

 

[Richard Schollar]

Assuming your Excel uses ; as argument separators then that formula should work (there is a spurious } on the end of it - you should remove that before you try and enter it).

Remember to confirm by holding down Ctrl and Shift and then tapping Enter (whilst Ctrl+Shift are still held down).

 

[yank1221]

Assuming your Excel uses ; as argument separators then that formula should work (there is a spurious } on the end of it - you should remove that before you try and enter it).

Remember to confirm by holding down Ctrl and Shift and then tapping Enter (whilst Ctrl+Shift are still held down).

I see, I am not see on the below, Right now can work Very Well, THank You Very much Friend

Regards
Andreas