Average formula help

[ranthrave]

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;">OthersPls.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​

 

[Marcelo Branco]

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.

Nice!

M.

 

[T. Valko]

Nice!

M.

Thanks!

I should've thought of this the first time around!

 

[T. Valko]

Thanks again sir

You're welcome!