Hello Board,
I have 2 questions for the sample data posted below:
<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"> <col style="width: 69px;"> <col style="width: 97px;"> <col style="width: 56px;"> <col style="width: 56px;"> <col style="width: 56px;"> <col style="width: 89px;"> <col style="width: 67px;"> <col style="width: 64px;"> <col style="width: 12px;"> <col style="width: 79px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td> <td>K</td> <td>L</td> <td>M</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>
</td> <td>
</td> <td>
</td> <td style="font-weight: bold; text-align: center;">Engr 1</td> <td style="font-weight: bold; text-align: center;">Engr 2</td> <td style="font-weight: bold; text-align: center;">Engr 3</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="font-weight: bold;">Rate:</td> <td>
</td> <td>
</td> <td style="text-align: right;">$140.50 </td> <td style="text-align: right;">$120.00 </td> <td style="text-align: right;">$100.00 </td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="font-weight: bold;">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="font-weight: bold; text-align: center;" colspan="3">Desired Result</td> <td>
</td> <td style="font-weight: bold; text-align: center;" colspan="3">Try 1</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="font-weight: bold; text-align: center;">REF NO.</td> <td style="font-weight: bold; text-align: center;">ITEM NO.</td> <td style="font-weight: bold;">DESCRIPTION</td> <td style="font-weight: bold; text-align: center;">Engr 1</td> <td style="font-weight: bold; text-align: center;">Engr 2</td> <td style="font-weight: bold; text-align: center;">Engr 3</td> <td style="font-weight: bold; text-align: center;">Sub-Total &</td> <td style="font-weight: bold; text-align: center;">% Effort</td> <td style="font-weight: bold; text-align: center;">% Effort</td> <td>
</td> <td style="font-weight: bold; text-align: center;">Col G</td> <td style="font-weight: bold; text-align: center;">Col H</td> <td style="font-weight: bold; text-align: center;">Col I</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold; text-align: center;">(Hrs)</td> <td style="font-weight: bold; text-align: center;">(Hrs)</td> <td style="font-weight: bold; text-align: center;">(Hrs)</td> <td style="font-weight: bold; text-align: center;">Task $</td> <td style="font-weight: bold; text-align: center;">(by item)</td> <td style="font-weight: bold; text-align: center;">(by ref)</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="font-weight: bold; text-align: center;">1</td> <td style="font-weight: bold;">
</td> <td>asdfwreqwet</td> <td>
</td> <td>
</td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">$5,387.50 </td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">28.57%</td> <td>
</td> <td style="text-align: right;">$5,387.50 </td> <td>
</td> <td style="text-align: right;">28.57%</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">a</td> <td style="text-align: left;">abc</td> <td style="text-align: center;">7</td> <td style="text-align: center;">8</td> <td style="text-align: center;">9</td> <td style="text-align: right;">$2,843.50 </td> <td style="text-align: right;">52.78%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$2,843.50 </td> <td style="text-align: right;">52.78%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">b</td> <td style="text-align: left;">xyz</td> <td style="text-align: center;">8</td> <td style="text-align: center;">6</td> <td style="text-align: center;">7</td> <td style="text-align: right;">$2,544.00 </td> <td style="text-align: right;">47.22%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$2,544.00 </td> <td style="text-align: right;">47.22%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td style="font-weight: bold; text-align: center;">2</td> <td>
</td> <td>qwetrywertq</td> <td>
</td> <td>
</td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">$3,165.00 </td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">16.78%</td> <td>
</td> <td style="text-align: right;">$3,165.00 </td> <td>
</td> <td style="text-align: right;">16.78%</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">a</td> <td style="text-align: left;">asd</td> <td style="text-align: center;">10</td> <td style="text-align: center;">8</td> <td style="text-align: center;">8</td> <td style="text-align: right;">$3,165.00 </td> <td style="text-align: right;">100.00%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$3,165.00 </td> <td style="text-align: right;">100.00%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td style="font-weight: bold; text-align: center;">3</td> <td>
</td> <td>ywertwrywry</td> <td>
</td> <td>
</td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">$5,002.50 </td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">26.53%</td> <td>
</td> <td style="text-align: right;">$5,002.50 </td> <td>
</td> <td style="text-align: right;">26.53%</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">a</td> <td style="text-align: left;">qwr</td> <td style="text-align: center;">1</td> <td style="text-align: center;">3</td> <td style="text-align: center;">10</td> <td style="text-align: right;">$1,500.50 </td> <td style="text-align: right;">30.00%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$1,500.50 </td> <td style="text-align: right;">30.00%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">b</td> <td style="text-align: left;">treq</td> <td style="text-align: center;">2</td> <td style="text-align: center;">7</td> <td style="text-align: center;">10</td> <td style="text-align: right;">$2,121.00 </td> <td style="text-align: right;">42.40%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$2,121.00 </td> <td style="text-align: right;">42.40%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">c</td> <td style="text-align: left;">grtw</td> <td style="text-align: center;">2</td> <td style="text-align: center;">5</td> <td style="text-align: center;">5</td> <td style="text-align: right;">$1,381.00 </td> <td style="text-align: right;">27.61%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$1,381.00 </td> <td style="text-align: right;">27.61%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td> <td style="font-weight: bold; text-align: center;">4</td> <td>
</td> <td>weryweryweyr</td> <td>
</td> <td>
</td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">$5,302.50 </td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">28.12%</td> <td>
</td> <td style="text-align: right;">$5,302.50 </td> <td>
</td> <td style="text-align: right;">28.12%</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">a</td> <td style="text-align: left;">bndt</td> <td style="text-align: center;">1</td> <td style="text-align: center;">9</td> <td style="text-align: center;">8</td> <td style="text-align: right;">$2,020.50 </td> <td style="text-align: right;">38.10%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$2,020.50 </td> <td style="text-align: right;">38.10%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">b</td> <td style="text-align: left;">agytr</td> <td style="text-align: center;">2</td> <td style="text-align: center;">6</td> <td style="text-align: center;">5</td> <td style="text-align: right;">$1,501.00 </td> <td style="text-align: right;">28.31%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$1,501.00 </td> <td style="text-align: right;">28.31%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">19</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">c</td> <td style="text-align: left;">httyu</td> <td style="text-align: center;">2</td> <td style="text-align: center;">5</td> <td style="text-align: center;">9</td> <td style="text-align: right;">$1,781.00 </td> <td style="text-align: right;">33.59%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$1,781.00 </td> <td style="text-align: right;">33.59%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">20</td> <td style="font-weight: bold; text-align: center;">5</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">21</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">22</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">23</td> <td style="font-weight: bold;">TOTAL</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold; text-align: right;">$18,857.50 </td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold; text-align: right;">100.00%</td> <td>
</td> <td style="text-align: right;">$18,857.50 </td> <td>
</td> <td style="text-align: right;">100.00%</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>K7</td> <td>=IF(A7<>"",SUM(OFFSET(K7,1,,MATCH(A7+1,$A$7:$A$20,0)-MATCH(A7,$A$7:$A$20,0)-1,)),SUMPRODUCT(D7:F7,$D$2:$F$2))</td></tr> <tr> <td>L7</td> <td>=IF(B7="","",K7/LOOKUP(100000000000,$A$7:A7,$K$7:K7))</td></tr> <tr> <td>M7</td> <td>=IF(B7="",K7/$K$23,"")</td></tr></tbody></table></td></tr></tbody></table>
(These formula are then copied down)
and
K23 =SUM(K7:K22)/2
Columns A through F is input, Columns G though I is the desired result and Columns K through M is the formula I tried.
The idea is to estimate hours and cost for a project. As you can see, there are bunch of tasks and sub tasks for each tasks.
Question 1:
I would like to find cost for each task by summing the estimated cost for each sub-tasks. Further, I also want to find the % effort of each task for the project and % effort of each sub-task for a task. The formula shown above works fine but I get bunch of N/A errors if I do not have 5 in cell A20. I understand the reason: When the match function tries to match 4+1=5 in column A, it returns N/A because 5 does not exist in column A. What I would like is a formula in column K that can take care of this issue. I think I also need new formula for column L too.
Question 2:
I want to compute total cost for each task without computing cost of each sub-task. I want something like =SUMPRODUCT(D8:F9,$D$2:$F$2) in cell K7 (which will return value error becacuse of different array sizes). How can I do this?
Any help is appreciated.
Thanks
I have 2 questions for the sample data posted below:
<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"> <col style="width: 69px;"> <col style="width: 97px;"> <col style="width: 56px;"> <col style="width: 56px;"> <col style="width: 56px;"> <col style="width: 89px;"> <col style="width: 67px;"> <col style="width: 64px;"> <col style="width: 12px;"> <col style="width: 79px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td> <td>K</td> <td>L</td> <td>M</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>
</td> <td>
</td> <td>
</td> <td style="font-weight: bold; text-align: center;">Engr 1</td> <td style="font-weight: bold; text-align: center;">Engr 2</td> <td style="font-weight: bold; text-align: center;">Engr 3</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="font-weight: bold;">Rate:</td> <td>
</td> <td>
</td> <td style="text-align: right;">$140.50 </td> <td style="text-align: right;">$120.00 </td> <td style="text-align: right;">$100.00 </td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="font-weight: bold;">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="font-weight: bold; text-align: center;" colspan="3">Desired Result</td> <td>
</td> <td style="font-weight: bold; text-align: center;" colspan="3">Try 1</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="font-weight: bold; text-align: center;">REF NO.</td> <td style="font-weight: bold; text-align: center;">ITEM NO.</td> <td style="font-weight: bold;">DESCRIPTION</td> <td style="font-weight: bold; text-align: center;">Engr 1</td> <td style="font-weight: bold; text-align: center;">Engr 2</td> <td style="font-weight: bold; text-align: center;">Engr 3</td> <td style="font-weight: bold; text-align: center;">Sub-Total &</td> <td style="font-weight: bold; text-align: center;">% Effort</td> <td style="font-weight: bold; text-align: center;">% Effort</td> <td>
</td> <td style="font-weight: bold; text-align: center;">Col G</td> <td style="font-weight: bold; text-align: center;">Col H</td> <td style="font-weight: bold; text-align: center;">Col I</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold; text-align: center;">(Hrs)</td> <td style="font-weight: bold; text-align: center;">(Hrs)</td> <td style="font-weight: bold; text-align: center;">(Hrs)</td> <td style="font-weight: bold; text-align: center;">Task $</td> <td style="font-weight: bold; text-align: center;">(by item)</td> <td style="font-weight: bold; text-align: center;">(by ref)</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="font-weight: bold; text-align: center;">1</td> <td style="font-weight: bold;">
</td> <td>asdfwreqwet</td> <td>
</td> <td>
</td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">$5,387.50 </td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">28.57%</td> <td>
</td> <td style="text-align: right;">$5,387.50 </td> <td>
</td> <td style="text-align: right;">28.57%</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">a</td> <td style="text-align: left;">abc</td> <td style="text-align: center;">7</td> <td style="text-align: center;">8</td> <td style="text-align: center;">9</td> <td style="text-align: right;">$2,843.50 </td> <td style="text-align: right;">52.78%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$2,843.50 </td> <td style="text-align: right;">52.78%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">b</td> <td style="text-align: left;">xyz</td> <td style="text-align: center;">8</td> <td style="text-align: center;">6</td> <td style="text-align: center;">7</td> <td style="text-align: right;">$2,544.00 </td> <td style="text-align: right;">47.22%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$2,544.00 </td> <td style="text-align: right;">47.22%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td style="font-weight: bold; text-align: center;">2</td> <td>
</td> <td>qwetrywertq</td> <td>
</td> <td>
</td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">$3,165.00 </td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">16.78%</td> <td>
</td> <td style="text-align: right;">$3,165.00 </td> <td>
</td> <td style="text-align: right;">16.78%</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">a</td> <td style="text-align: left;">asd</td> <td style="text-align: center;">10</td> <td style="text-align: center;">8</td> <td style="text-align: center;">8</td> <td style="text-align: right;">$3,165.00 </td> <td style="text-align: right;">100.00%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$3,165.00 </td> <td style="text-align: right;">100.00%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td style="font-weight: bold; text-align: center;">3</td> <td>
</td> <td>ywertwrywry</td> <td>
</td> <td>
</td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">$5,002.50 </td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">26.53%</td> <td>
</td> <td style="text-align: right;">$5,002.50 </td> <td>
</td> <td style="text-align: right;">26.53%</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">a</td> <td style="text-align: left;">qwr</td> <td style="text-align: center;">1</td> <td style="text-align: center;">3</td> <td style="text-align: center;">10</td> <td style="text-align: right;">$1,500.50 </td> <td style="text-align: right;">30.00%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$1,500.50 </td> <td style="text-align: right;">30.00%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">b</td> <td style="text-align: left;">treq</td> <td style="text-align: center;">2</td> <td style="text-align: center;">7</td> <td style="text-align: center;">10</td> <td style="text-align: right;">$2,121.00 </td> <td style="text-align: right;">42.40%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$2,121.00 </td> <td style="text-align: right;">42.40%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">c</td> <td style="text-align: left;">grtw</td> <td style="text-align: center;">2</td> <td style="text-align: center;">5</td> <td style="text-align: center;">5</td> <td style="text-align: right;">$1,381.00 </td> <td style="text-align: right;">27.61%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$1,381.00 </td> <td style="text-align: right;">27.61%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td> <td style="font-weight: bold; text-align: center;">4</td> <td>
</td> <td>weryweryweyr</td> <td>
</td> <td>
</td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">$5,302.50 </td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">28.12%</td> <td>
</td> <td style="text-align: right;">$5,302.50 </td> <td>
</td> <td style="text-align: right;">28.12%</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">a</td> <td style="text-align: left;">bndt</td> <td style="text-align: center;">1</td> <td style="text-align: center;">9</td> <td style="text-align: center;">8</td> <td style="text-align: right;">$2,020.50 </td> <td style="text-align: right;">38.10%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$2,020.50 </td> <td style="text-align: right;">38.10%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">b</td> <td style="text-align: left;">agytr</td> <td style="text-align: center;">2</td> <td style="text-align: center;">6</td> <td style="text-align: center;">5</td> <td style="text-align: right;">$1,501.00 </td> <td style="text-align: right;">28.31%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$1,501.00 </td> <td style="text-align: right;">28.31%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">19</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">c</td> <td style="text-align: left;">httyu</td> <td style="text-align: center;">2</td> <td style="text-align: center;">5</td> <td style="text-align: center;">9</td> <td style="text-align: right;">$1,781.00 </td> <td style="text-align: right;">33.59%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$1,781.00 </td> <td style="text-align: right;">33.59%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">20</td> <td style="font-weight: bold; text-align: center;">5</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">21</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">22</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">23</td> <td style="font-weight: bold;">TOTAL</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold; text-align: right;">$18,857.50 </td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold; text-align: right;">100.00%</td> <td>
</td> <td style="text-align: right;">$18,857.50 </td> <td>
</td> <td style="text-align: right;">100.00%</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>K7</td> <td>=IF(A7<>"",SUM(OFFSET(K7,1,,MATCH(A7+1,$A$7:$A$20,0)-MATCH(A7,$A$7:$A$20,0)-1,)),SUMPRODUCT(D7:F7,$D$2:$F$2))</td></tr> <tr> <td>L7</td> <td>=IF(B7="","",K7/LOOKUP(100000000000,$A$7:A7,$K$7:K7))</td></tr> <tr> <td>M7</td> <td>=IF(B7="",K7/$K$23,"")</td></tr></tbody></table></td></tr></tbody></table>
(These formula are then copied down)
and
K23 =SUM(K7:K22)/2
Columns A through F is input, Columns G though I is the desired result and Columns K through M is the formula I tried.
The idea is to estimate hours and cost for a project. As you can see, there are bunch of tasks and sub tasks for each tasks.
Question 1:
I would like to find cost for each task by summing the estimated cost for each sub-tasks. Further, I also want to find the % effort of each task for the project and % effort of each sub-task for a task. The formula shown above works fine but I get bunch of N/A errors if I do not have 5 in cell A20. I understand the reason: When the match function tries to match 4+1=5 in column A, it returns N/A because 5 does not exist in column A. What I would like is a formula in column K that can take care of this issue. I think I also need new formula for column L too.
Question 2:
I want to compute total cost for each task without computing cost of each sub-task. I want something like =SUMPRODUCT(D8:F9,$D$2:$F$2) in cell K7 (which will return value error becacuse of different array sizes). How can I do this?
Any help is appreciated.
Thanks
Last edited: