Tough to explain this:
I need to calculate a value for cell J8 in the table below.
I8 is the first cell containing a W in several cells and J8 needs to add up all
the negative values in J up until the last row with a W (which is column 1).
So it needs to add up J2 through J7. How can I do this by formula?
I need to do this for 500 rows and sometimes there are 4 L's in a row, sometimes 6
such as in this example.... can Excel do this?
Thanks,
Simon
<TABLE BORDER><TR>
<TH> </TH> <TH>H</TH> <TH>I</TH> <TH>J</TH>
</TR>
<TR><TD WIDTH=100>1</TD><TD WIDTH=100>-1.13</TD> <TD WIDTH=100>W</TD> <TD WIDTH>5</TD></TR>
<TR><TD WIDTH=100>2</TD><TD WIDTH=100>-1.17</TD> <TD WIDTH>L</TD> <TD WIDTH>-5.85</TD></TR>
<TR><TD WIDTH=100>3</TD><TD WIDTH=100>-1.17</TD> <TD WIDTH>L</TD> <TD WIDTH>-11.7</TD></TR>
<TR><TD WIDTH=100>4</TD><TD WIDTH=100>-1.08</TD> <TD WIDTH>L</TD> <TD WIDTH>-22.95</TD></TR>
<TR><TD WIDTH=100>5</TD><TD WIDTH=100>-1.18</TD> <TD WIDTH>L</TD> <TD WIDTH>-46.4</TD></TR>
<TR><TD WIDTH=100>6</TD><TD WIDTH=100>-1.01</TD> <TD WIDTH>L</TD> <TD WIDTH>-91.95</TD></TR>
<TR><TD WIDTH=100>7</TD><TD WIDTH=100>-1.25</TD> <TD WIDTH>L</TD> <TD WIDTH>-185.1</TD></TR>
<TR><TD WIDTH=100>8</TD><TD WIDTH=100>-1.08</TD> <TD WIDTH>W</TD> <TD WIDTH> </TD></TR>
<TR><TD WIDTH=100>9</TD><TD WIDTH=100>-1.14</TD> <TD WIDTH>W</TD> <TD WIDTH> </TD></TR>
</TABLE>
I need to calculate a value for cell J8 in the table below.
I8 is the first cell containing a W in several cells and J8 needs to add up all
the negative values in J up until the last row with a W (which is column 1).
So it needs to add up J2 through J7. How can I do this by formula?
I need to do this for 500 rows and sometimes there are 4 L's in a row, sometimes 6
such as in this example.... can Excel do this?
Thanks,
Simon
<TABLE BORDER><TR>
<TH> </TH> <TH>H</TH> <TH>I</TH> <TH>J</TH>
</TR>
<TR><TD WIDTH=100>1</TD><TD WIDTH=100>-1.13</TD> <TD WIDTH=100>W</TD> <TD WIDTH>5</TD></TR>
<TR><TD WIDTH=100>2</TD><TD WIDTH=100>-1.17</TD> <TD WIDTH>L</TD> <TD WIDTH>-5.85</TD></TR>
<TR><TD WIDTH=100>3</TD><TD WIDTH=100>-1.17</TD> <TD WIDTH>L</TD> <TD WIDTH>-11.7</TD></TR>
<TR><TD WIDTH=100>4</TD><TD WIDTH=100>-1.08</TD> <TD WIDTH>L</TD> <TD WIDTH>-22.95</TD></TR>
<TR><TD WIDTH=100>5</TD><TD WIDTH=100>-1.18</TD> <TD WIDTH>L</TD> <TD WIDTH>-46.4</TD></TR>
<TR><TD WIDTH=100>6</TD><TD WIDTH=100>-1.01</TD> <TD WIDTH>L</TD> <TD WIDTH>-91.95</TD></TR>
<TR><TD WIDTH=100>7</TD><TD WIDTH=100>-1.25</TD> <TD WIDTH>L</TD> <TD WIDTH>-185.1</TD></TR>
<TR><TD WIDTH=100>8</TD><TD WIDTH=100>-1.08</TD> <TD WIDTH>W</TD> <TD WIDTH> </TD></TR>
<TR><TD WIDTH=100>9</TD><TD WIDTH=100>-1.14</TD> <TD WIDTH>W</TD> <TD WIDTH> </TD></TR>
</TABLE>