Average formula help

R

ranthrave

Board Regular
Joined
Aug 26, 2010
Messages
141
Hello sir/maam! Kindly help me how to write the average formula in cell D31 from the data validation drop down list (d14:d29). The answers were based from the rational scale. Average should be 2.81.



Excel 2007<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(166, 170, 182); border-collapse: collapse;" cellpadding="2.5px" rules="all"><colgroup><col style="background-color: rgb(224, 224, 240);" width="25px"><col><col><col></colgroup><thead><tr style="background-color: rgb(224, 224, 240); text-align: center; color: rgb(22, 17, 32);"><th>
</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">3</td><td style="">Rational Scale for Criteria five</td><td style="">
</td><td style="">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">4</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">5</td><td style="text-align: center;">Scale</td><td style="text-align: center;">Definition</td><td style="text-align: center;">Explanation</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">6</td><td style="text-align: center;">1</td><td style="">Won't be able to meet</td><td style="">Documents won't be able to prepare</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">7</td><td style="text-align: center;">2</td><td style="">Missed</td><td style="">Documents are not yet prepared</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">8</td><td style="text-align: center;">3</td><td style="">Nearly Met</td><td style="">Documents are properly prepared but not complete</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">9</td><td style="text-align: center;">4</td><td style="">Met</td><td style="">Documents are complete and properly prepared.</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">10</td><td style="text-align: right; border-bottom: 1px solid black;">
</td><td style="text-align: right; border-bottom: 1px solid black;">
</td><td style="text-align: right; border-bottom: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">11</td><td style="font-weight: bold; border-top: 1px solid black; border-left: 1px solid black;">Business Permit/Accreditation</td><td style="text-align: right; border-top: 1px solid black;">
</td><td style="text-align: right; border-top: 1px solid black; border-right: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">12</td><td style="text-align: right; border-left: 1px solid black;">
</td><td style="text-align: right;">
</td><td style="text-align: right; border-right: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">13</td><td style="font-weight: bold; border-left: 1px solid black;">A. Requirements for securing business permit</td><td style="text-align: right;">
</td><td style="font-weight: bold; border-right: 1px solid black;">Answers</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">14</td><td style="border-left: 1px solid black;">SEC Registration and articles of Incorporation</td><td style="text-align: right;">
</td><td style="border-right: 1px solid black;">Nearly Met</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">15</td><td style="border-left: 1px solid black;">SSS Clearance</td><td style="text-align: right;">
</td><td style="border-right: 1px solid black;">Nearly Met</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">16</td><td style="border-left: 1px solid black;">BIR Registration</td><td style="text-align: right;">
</td><td style="border-right: 1px solid black;">Nearly Met</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">17</td><td style="border-left: 1px solid black;">Certification as Donee Institution from PCNC</td><td style="text-align: right;">
</td><td style="border-right: 1px solid black;">Nearly Met</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">18</td><td style="border-left: 1px solid black;">Latest Audited statements</td><td style="text-align: right;">
</td><td style="border-right: 1px solid black;">Won't be able to meet</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">19</td><td style="border-left: 1px solid black;">List of Current members of the Board of Trustees</td><td style="text-align: right;">
</td><td style="border-right: 1px solid black;">Nearly Met</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">20</td><td style="border-left: 1px solid black;">Others:( pls. Indicate)</td><td style="text-align: right;">
</td><td style="text-align: right; border-right: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">21</td><td style="text-align: right; border-left: 1px solid black;">
</td><td style="text-align: right;">
</td><td style="text-align: right; border-right: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">22</td><td style="text-align: right; border-left: 1px solid black;">
</td><td style="text-align: right;">
</td><td style="text-align: right; border-right: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">23</td><td style="font-weight: bold; border-left: 1px solid black;">B. Requirements for accreditation</td><td style="text-align: right;">
</td><td style="text-align: right; border-right: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">24</td><td style="border-left: 1px solid black;">SEC Registration and Articles of Incorpotation</td><td style="text-align: right;">
</td><td style="border-right: 1px solid black;">Met</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">25</td><td style="border-left: 1px solid black;">Organizational profile, programs & services</td><td style="text-align: right;">
</td><td style="border-right: 1px solid black;">Missed</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">26</td><td style="border-left: 1px solid black;">List of current members of the Board of Trustees</td><td style="text-align: right;">
</td><td style="border-right: 1px solid black;">Nearly Met</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">27</td><td style="border-left: 1px solid black;">Certificate as Done Institution from PCNC</td><td style="text-align: right;">
</td><td style="border-right: 1px solid black;">Nearly Met</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">28</td><td style="border-left: 1px solid black;">Photocopy accredited by provincial govt.</td><td style="text-align: right;">
</td><td style="border-right: 1px solid black;">Nearly Met</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">29</td><td style="border-bottom: 1px solid black; border-left: 1px solid black;">Others:(Pls.indicate)</td><td style="text-align: right; border-bottom: 1px solid black;">
</td><td style="text-align: right; border-right: 1px solid black; border-bottom: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">30</td><td style="text-align: right; border-top: 1px solid black;">
</td><td style="text-align: right; border-top: 1px solid black;">
</td><td style="text-align: right; border-top: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">31</td><td style="text-align: right;">
</td><td style="font-weight: bold;">Average</td><td style="text-align: right;">
</td></tr></tbody></table>
Sheet3​


 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
ranthrave said:
Hello sir/maam! Kindly help me how to write the average formula in cell D31 from the data validation drop down list (d14:d29). The answers were based from the rational scale. Average should be 2.81.



Excel 2007<TABLE style="BORDER-RIGHT: rgb(166,170,182) 1px solid; BORDER-TOP: rgb(166,170,182) 1px solid; BORDER-LEFT: rgb(166,170,182) 1px solid; BORDER-BOTTOM: rgb(166,170,182) 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: rgb(255,255,255)" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: rgb(224,224,240)" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: rgb(22,17,32); BACKGROUND-COLOR: rgb(224,224,240); TEXT-ALIGN: center"><TH>

</TH><TH>B</TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">3</TD><TD>Rational Scale for Criteria five</TD><TD></B>

</TD><TD></B>

</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right"></B>

</TD><TD style="TEXT-ALIGN: right"></B>

</TD><TD style="TEXT-ALIGN: right"></B>

</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">Scale</TD><TD style="TEXT-ALIGN: center">Definition</TD><TD style="TEXT-ALIGN: center">Explanation</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">1</TD><TD>Won't be able to meet</TD><TD>Documents won't be able to prepare</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">2</TD><TD>Missed</TD><TD>Documents are not yet prepared</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">3</TD><TD>Nearly Met</TD><TD>Documents are properly prepared but not complete</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">4</TD><TD>Met</TD><TD>Documents are complete and properly prepared.</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">10</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></B>

</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">11</TD><TD style="BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-LEFT: black 1px solid">Business Permit/Accreditation</TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></B>

</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">12</TD><TD style="BORDER-LEFT: black 1px solid; TEXT-ALIGN: right"></B>

</TD><TD style="TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-RIGHT: black 1px solid; TEXT-ALIGN: right"></B>

</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">13</TD><TD style="FONT-WEIGHT: bold; BORDER-LEFT: black 1px solid">A. Requirements for securing business permit</TD><TD style="TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-RIGHT: black 1px solid; FONT-WEIGHT: bold">Answers</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">14</TD><TD style="BORDER-LEFT: black 1px solid">SEC Registration and articles of Incorporation</TD><TD style="TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-RIGHT: black 1px solid">Nearly Met</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">15</TD><TD style="BORDER-LEFT: black 1px solid">SSS Clearance</TD><TD style="TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-RIGHT: black 1px solid">Nearly Met</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">16</TD><TD style="BORDER-LEFT: black 1px solid">BIR Registration</TD><TD style="TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-RIGHT: black 1px solid">Nearly Met</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">17</TD><TD style="BORDER-LEFT: black 1px solid">Certification as Donee Institution from PCNC</TD><TD style="TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-RIGHT: black 1px solid">Nearly Met</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">18</TD><TD style="BORDER-LEFT: black 1px solid">Latest Audited statements</TD><TD style="TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-RIGHT: black 1px solid">Won't be able to meet</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">19</TD><TD style="BORDER-LEFT: black 1px solid">List of Current members of the Board of Trustees</TD><TD style="TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-RIGHT: black 1px solid">Nearly Met</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">20</TD><TD style="BORDER-LEFT: black 1px solid">Others:( pls. Indicate)</TD><TD style="TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-RIGHT: black 1px solid; TEXT-ALIGN: right"></B>

</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">21</TD><TD style="BORDER-LEFT: black 1px solid; TEXT-ALIGN: right"></B>

</TD><TD style="TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-RIGHT: black 1px solid; TEXT-ALIGN: right"></B>

</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">22</TD><TD style="BORDER-LEFT: black 1px solid; TEXT-ALIGN: right"></B>

</TD><TD style="TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-RIGHT: black 1px solid; TEXT-ALIGN: right"></B>

</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">23</TD><TD style="FONT-WEIGHT: bold; BORDER-LEFT: black 1px solid">B. Requirements for accreditation</TD><TD style="TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-RIGHT: black 1px solid; TEXT-ALIGN: right"></B>

</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">24</TD><TD style="BORDER-LEFT: black 1px solid">SEC Registration and Articles of Incorpotation</TD><TD style="TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-RIGHT: black 1px solid">Met</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">25</TD><TD style="BORDER-LEFT: black 1px solid">Organizational profile, programs & services</TD><TD style="TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-RIGHT: black 1px solid">Missed</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">26</TD><TD style="BORDER-LEFT: black 1px solid">List of current members of the Board of Trustees</TD><TD style="TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-RIGHT: black 1px solid">Nearly Met</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">27</TD><TD style="BORDER-LEFT: black 1px solid">Certificate as Done Institution from PCNC</TD><TD style="TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-RIGHT: black 1px solid">Nearly Met</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">28</TD><TD style="BORDER-LEFT: black 1px solid">Photocopy accredited by provincial govt.</TD><TD style="TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-RIGHT: black 1px solid">Nearly Met</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">29</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid">Others:(Pls.indicate)</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></B>

</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">30</TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></B>

</TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></B>

</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">31</TD><TD style="TEXT-ALIGN: right"></B>

</TD><TD style="FONT-WEIGHT: bold">Average</TD><TD style="TEXT-ALIGN: right"></B>
​
</TD></TR></TBODY></TABLE>Sheet3</B>
Click to expand...
Try this...

Book1
AB
1PointsScale
21A
32B
43C
54D
6__
7_B
8_B
9_C
10_A
11_A
12__
13__
14_D
15_D
16_D
17_C
18_A
Sheet1

This array formula**:

=AVERAGE(LOOKUP(B7:B11,B2:B5,A2:A5),LOOKUP(B14:B18,B2:B5,A2:A5))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
 
Upvote 0
T. Valko said:
Try this...

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 2px solid; BORDER-LEFT: #000000 2px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Points</TD><TD style="BORDER-RIGHT: #000000 2px solid; BORDER-TOP: #000000 2px solid; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Scale</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 2px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 2px solid; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 2px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: #000000 2px solid; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 2px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">3</TD><TD style="BORDER-RIGHT: #000000 2px solid; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">C</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 2px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 2px solid; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 2px; BORDER-RIGHT: #000000 2px solid; BORDER-LEFT-WIDTH: 2px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 2px solid; TEXT-ALIGN: center">D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">C</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">C</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD></TR></TBODY></TABLE>


This array formula**:

=AVERAGE(LOOKUP(B7:B11,B2:B5,A2:A5),LOOKUP(B14:B18,B2:B5,A2:A5))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Click to expand...
Note that the answer scale must be sorted in ascending order for this method to work properly.
 
Upvote 0
Maybe this

=SUM(COUNTIF($D$14:$D$28,{"Won't*";"Missed";"Nearly*";"Met"})*{1;2;3;4})/COUNTA($D$14:$D$28)

HTH

M.
 
Upvote 0
If is possible some entry in D20, the formula above should be adjusted to

=SUM(COUNTIF($D$14:$D$28,{"Won't*";"Missed";"Nearly*";"Met"})*{1;2;3;4})/(COUNTA($D$14:$D$28)-COUNTA($D$20))

M.
 
Upvote 0
ranthrave said:
Thank you sir. In your example what if I Don't have an answer in D11. (How could I properly work on it "average')?
Click to expand...
Let's try a different approach.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 2px solid; BORDER-LEFT: #000000 2px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Points</TD><TD style="BORDER-RIGHT: #000000 2px solid; BORDER-TOP: #000000 2px solid; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Scale</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 2px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 2px solid; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 2px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: #000000 2px solid; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 2px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">3</TD><TD style="BORDER-RIGHT: #000000 2px solid; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">C</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 2px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 2px solid; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 2px; BORDER-RIGHT: #000000 2px solid; BORDER-LEFT-WIDTH: 2px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 2px solid; TEXT-ALIGN: center">D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">C</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">C</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD></TR></TBODY></TABLE>


This formula will ignore any empty cells:

=SUMPRODUCT(SUMIF(B2:B5,B7:B18,A2:A5))/COUNTA(B7:B18)

Also, the scale does not need to be sorted.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
624
smozgur
smozgur
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
90
Dazzybeeguy
D
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
Dan Wilson
  • Solved
Formula help.
2
Replies
10
Views
241
Jeffrey Mahoney
Jeffrey Mahoney
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V

Forum statistics

Threads
1,224,618
Messages
6,179,917
Members
452,949
Latest member
beartooth91

Share this page

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?

Disable AdBlock

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

Disable AdBlock Plus

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