I have a spreadsheet with Average Stocking holdings in Col B as well as an average of the total. The Stock is by branch and is split into two categories NV & UV. This is notes at the end of each branch in Col A for eg BR1NV, BR1UV
I want to use compute the average for each category. I tried using Averageif for NV as well as for UV, but when I total up the Averageifs (Cell B37), it is exactly double to the total of the average in cell B21
It would be appreciated if someone could assist me
See sample data below
<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:96px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td></tr><tr style="height:36px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-weight:bold; ">Branch</td><td style="font-weight:bold; ">Average Days <br />in Stock</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >BR1NV</td><td style="text-align:right; ">208</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >BR1UV</td><td style="text-align:right; ">487</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >BR2NV</td><td style="text-align:right; ">242</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >BR2UV</td><td style="text-align:right; ">511</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >BR3NV</td><td style="text-align:right; ">234</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >BR3UV</td><td style="text-align:right; ">337</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >BR4NV</td><td style="text-align:right; ">289</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >BR4UV</td><td style="text-align:right; ">407</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >BR5NV</td><td style="text-align:right; ">122</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >BR5UV</td><td style="text-align:right; ">321</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >BR6NV</td><td style="text-align:right; ">240</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >BR6UV</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >BR7NV</td><td style="text-align:right; ">183</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >BR7UV</td><td style="text-align:right; ">722</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >BR8NV</td><td style="text-align:right; ">207</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >BR8UV</td><td style="text-align:right; ">379</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td >BR9NV</td><td style="text-align:right; ">328</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td >BR9UV</td><td style="text-align:right; ">451</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >BR10NV</td><td style="text-align:right; ">215</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td >BR10UV</td><td style="text-align:right; ">374</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td >BR11NV</td><td style="text-align:right; ">402</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td >BR11UV</td><td style="text-align:right; ">480</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >BR12NV</td><td style="text-align:right; ">244</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td >BR12UV</td><td style="text-align:right; ">306</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td >BR13NV</td><td style="text-align:right; ">241</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td >BR13UV</td><td style="text-align:right; ">303</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td >*</td><td >*</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td style=" border-bottom-style:solid; border-bottom-width:2px; border-bottom-color:#000000; ">*</td><td style=" border-bottom-style:solid; border-bottom-width:2px; border-bottom-color:#000000; ">*</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >32</td><td style="font-weight:bold; border-bottom-style:solid; border-left-style:solid; border-bottom-width:2px; border-left-width:2px; border-bottom-color:#000000; border-left-color:#000000; ">Average</td><td style="font-weight:bold; text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; ">317</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td style=" border-bottom-style:solid; border-bottom-width:1px; border-bottom-color:#000000; ">*</td><td style=" border-bottom-style:solid; border-bottom-width:1px; border-bottom-color:#000000; ">*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td style="background-color:#00ccff; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">NV</td><td style="background-color:#00ccff; text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">243</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td style="background-color:#00ccff; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">UV</td><td style="background-color:#00ccff; text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">391</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >36</td><td style="background-color:#00ccff; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">*</td><td style="background-color:#00ccff; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >37</td><td style="background-color:#00ccff; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">check</td><td style="background-color:#00ccff; text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">633</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >38</td><td >*</td><td >*</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B32</td><td >=AVERAGE(B3:B31)</td></tr><tr><td >B34</td><td >=AVERAGEIF($A$4:$B$29,"*NV*",$B$4:$B$31)</td></tr><tr><td >B35</td><td >=AVERAGEIF($A$4:$B$29,"*UV*",$B$4:$B$31)</td></tr><tr><td >B37</td><td >=+B34+B35</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
I want to use compute the average for each category. I tried using Averageif for NV as well as for UV, but when I total up the Averageifs (Cell B37), it is exactly double to the total of the average in cell B21
It would be appreciated if someone could assist me
See sample data below
<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:96px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td></tr><tr style="height:36px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-weight:bold; ">Branch</td><td style="font-weight:bold; ">Average Days <br />in Stock</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >BR1NV</td><td style="text-align:right; ">208</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >BR1UV</td><td style="text-align:right; ">487</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >BR2NV</td><td style="text-align:right; ">242</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >BR2UV</td><td style="text-align:right; ">511</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >BR3NV</td><td style="text-align:right; ">234</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >BR3UV</td><td style="text-align:right; ">337</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >BR4NV</td><td style="text-align:right; ">289</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >BR4UV</td><td style="text-align:right; ">407</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >BR5NV</td><td style="text-align:right; ">122</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >BR5UV</td><td style="text-align:right; ">321</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >BR6NV</td><td style="text-align:right; ">240</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >BR6UV</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >BR7NV</td><td style="text-align:right; ">183</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >BR7UV</td><td style="text-align:right; ">722</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >BR8NV</td><td style="text-align:right; ">207</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >BR8UV</td><td style="text-align:right; ">379</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td >BR9NV</td><td style="text-align:right; ">328</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td >BR9UV</td><td style="text-align:right; ">451</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >BR10NV</td><td style="text-align:right; ">215</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td >BR10UV</td><td style="text-align:right; ">374</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td >BR11NV</td><td style="text-align:right; ">402</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td >BR11UV</td><td style="text-align:right; ">480</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >BR12NV</td><td style="text-align:right; ">244</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td >BR12UV</td><td style="text-align:right; ">306</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td >BR13NV</td><td style="text-align:right; ">241</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td >BR13UV</td><td style="text-align:right; ">303</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td >*</td><td >*</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td style=" border-bottom-style:solid; border-bottom-width:2px; border-bottom-color:#000000; ">*</td><td style=" border-bottom-style:solid; border-bottom-width:2px; border-bottom-color:#000000; ">*</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >32</td><td style="font-weight:bold; border-bottom-style:solid; border-left-style:solid; border-bottom-width:2px; border-left-width:2px; border-bottom-color:#000000; border-left-color:#000000; ">Average</td><td style="font-weight:bold; text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#000000; ">317</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td style=" border-bottom-style:solid; border-bottom-width:1px; border-bottom-color:#000000; ">*</td><td style=" border-bottom-style:solid; border-bottom-width:1px; border-bottom-color:#000000; ">*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td style="background-color:#00ccff; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">NV</td><td style="background-color:#00ccff; text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">243</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td style="background-color:#00ccff; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">UV</td><td style="background-color:#00ccff; text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">391</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >36</td><td style="background-color:#00ccff; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">*</td><td style="background-color:#00ccff; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >37</td><td style="background-color:#00ccff; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">check</td><td style="background-color:#00ccff; text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">633</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >38</td><td >*</td><td >*</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B32</td><td >=AVERAGE(B3:B31)</td></tr><tr><td >B34</td><td >=AVERAGEIF($A$4:$B$29,"*NV*",$B$4:$B$31)</td></tr><tr><td >B35</td><td >=AVERAGEIF($A$4:$B$29,"*UV*",$B$4:$B$31)</td></tr><tr><td >B37</td><td >=+B34+B35</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
