Mode Function

yank1221

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

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

DonkeyOte

MrExcel MVP
=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

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

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

New Member
=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

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

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

Replies
4
Views
106
Replies
5
Views
219
Replies
3
Views
310
Replies
7
Views
923
Replies
0
Views
93

1,191,030
Messages
5,984,232
Members
439,879
Latest member
KingGoulash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back