Combining calculation formula into one cell

[stevembe]

Hi everybody,

I have a spreadsheet with columns of workhours with headers as so:

<TABLE style="WIDTH: 384pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=512 x:str><COLGROUP><COL style="WIDTH: 48pt" span=8 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl22 height=17 width=64 align=right x:num>19.50</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl23 width=64 align=right x:num>22.20</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl23 width=64 align=right x:num>24.00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl23 width=64 align=right x:num>26.00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl23 width=64 align=right x:num>28.50</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl23 width=64 align=right x:num>29.60</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl23 width=64 align=right x:num>33.30</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl23 width=64 align=right x:num>37.00</TD></TR></TBODY></TABLE>

Underneath in the columns I have figures which denote the number of employees who worked those hours. For example if there were 2 people who worked 22.2Hrs there would be a figure 2 under that column. What I need to do is multiply the amount of employees by workhours less for the 37.00 column where the number gets added. The other difficulty I have is if there are numbers under more than one column, I can only see to do formulas for every single column. I have managed to split the formula up but only over three cells and I want to put it all into one e.g.

First cell

=SUM(A5*A1)+(B5*A1)+(C5*C1)+(D5*D1)+(E5*E1)+(F5*F1)+(G5*G1)

Second Cell

=SUM(I5/37)

Third cell

=SUM(J5+H5)

I need to combine all of the above formula actions into one cell and easily copy it into multiple rows down the spreadsheet.

Hope what I need is clear? Many thanks in advance

 

[CWatts]

What you're asking for sounds like it will be something we can help with quickly, but more information is needed.

Specifically, when you say "first cell" and "second cell" we don't know what the actuall cell address is.

Instead of saying "First cell" you can say "in cell A1 I have this formula" and clarify that would help.

Also, incldue column headings and row numbers with your example data and the more you can show, the easier it will be for us to help you.

(Below is an example of Excel Jeanie which will do the trick for you)

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">19.5</TD><TD style="TEXT-ALIGN: right">22.2</TD><TD style="TEXT-ALIGN: right">24</TD><TD style="TEXT-ALIGN: right">26</TD><TD style="TEXT-ALIGN: right">28.5</TD><TD style="TEXT-ALIGN: right">29.6</TD><TD style="TEXT-ALIGN: right">33.3</TD></TR></TBODY></TABLE>

\http://www.excel-jeanie-html.de/index.php

 

[West Man]

As CWatts has said clarification would help. But assuming that you desire the total hours plus the total hours divided by 37, then this may work:
=SUMPRODUCT((A1:G1),(A5:G5))*38/37

 

[stevembe]

Thanks for replies and sorry if I was not clear enough. Does this make my need any clearer:

<TABLE style="WIDTH: 720pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=960 x:str><COLGROUP><COL style="WIDTH: 48pt" span=15 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl24 height=17 width=64 align=right x:num>19.50</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl25 width=64 align=right x:num>22.20</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl25 width=64 align=right x:num>24.00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl25 width=64 align=right x:num>26.00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl25 width=64 align=right x:num>28.50</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl25 width=64 align=right x:num>29.60</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl25 width=64 align=right x:num>33.30</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl25 width=64 align=right x:num>37.00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64>Totals</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl32 align=right x:num>5.6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 288pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 rowSpan=7 width=384 colSpan=6>The total column needs to calculate the sum of A2*A1 + the sum of B2*B1 + the sum of C2*C1 + the sum of D2*D1 + the sum of E2*E1 + the sum of F2*F1 + the sum of G2*G1 then divide by this figure by 37 before adding H2. The end result should be 5.6 but I can not get the formula to do this, and I also need all the other rows to do the same calculation.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl30></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl26 height=17 align=right x:num>3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl26 height=17 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl27 align=right x:num>31</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl28 height=17 align=right x:num>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>31</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR></TBODY></TABLE>

 

[stevembe]

I did get this far:

=SUM(A2*A1)+SUM(B2*B1)+SUM(C2*C1)+SUM(D2*D1)+SUM(E2*E1)+SUM(F2*F1)+SUM(G2*G1)/37

Whilst the additions work the divide by 37 does not alter the result

 

[stevembe]

Been messing with this one and having got to:

=SUM(A2*A1)+SUM(B2*B1)+SUM(C2*C1)+SUM(D2*D1)+SUM(E2*E1)+SUM(F2*F1)+SUM(G2*G1)

I added /37 but that does not do the divide I expected so in an adjacent cell I put:

=SUM(D15/37)+H2

I want to combine both formulas, I am sure I am being an idiot but I just can't get it to work.

Any advice hugely appreciated

 

[mmmarks]

Hi,
Try this one
Just paste it in Total column
=((($A$1*A2)+($B$1*B2)+($C$1*C2)+($D$1*D2)+($E$1*E2)+($F$1*F2)+($G$1*G2))/$H$1)+H2

 

[stevembe]

Hi,
Try this one
Just paste it in Total column
=((($A$1*A2)+($B$1*B2)+($C$1*C2)+($D$1*D2)+($E$1*E2)+($F$1*F2)+($G$1*G2))/$H$1)+H2

mmmarks, thank you, you are an absolute star, that works perfectly, thank you so much!

 

[mmmarks]

you are welcome!