Calculation II

[Mike]

A few days ago I asked for a formula to auto-calculate a column for ATTRITION
as follows:
col A
row 1 10,000
row 2 8,000
row 3 6,000
row 4
row 5 __________
Attrition 4,000

The formula in cell [A6] is =A1-OFFSET(A1,COUNT(A1:A5)-1,0) confirmed with
Ctrl+Shift+Enter instead of just with Enter.

Now I need to put a simple SUM formula in cells [A1,A2,A3,A4,A5] that sum
columns B,C,D,E across, for example: cell [A1] would contain the formula
SUM(B1:E1), yet still have cell [A6] auto-calculate the same as before. When
I put formulas in cells [A1,A2,A3,A4,A5] the formula in cell [A6] doesn't
function anymore. Does anyone know how to make this work? Thanks

 

[Bob Phillips]

Your formula doesn't (never) worked for me, but this does

=SUM(OFFSET(A1,1,,COUNT(A1:A5)-1,1))-A1

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Mike]

This is still not working how I need it to. I need the attrition to
auto-calculate by subtracting the last cell (row) entered from cell [A1]. As
in my example below, Attrition would be calculated by subtract cell [A3] from
[A1], since cell [A3] was the last row with a figure entered into it.
However, to make this even more complicated I now want to put a SUM formula
in cells [A1 thru A5], for example, the formula would be =SUM(B1:E1) for cell
[A1] and so forth. Is there a formula that will auto-calculate the Attrition
in a column of data,if that data is results of a formula?

 

[Bob Phillips]

Mike,

How about this then?

=A1-INDIRECT(ADDRESS(MAX((A1:A5<>0)*ROW(A1:A5)),1))

It is an array formula, so commit with Ctrl-Shift-Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)

This is still not working how I need it to. I need the attrition to
auto-calculate by subtracting the last cell (row) entered from cell [A1]. As
in my example below, Attrition would be calculated by subtract cell [A3] from
[A1], since cell [A3] was the last row with a figure entered into it.
However, to make this even more complicated I now want to put a SUM formula
in cells [A1 thru A5], for example, the formula would be =SUM(B1:E1) for cell
[A1] and so forth. Is there a formula that will auto-calculate the Attrition
in a column of data,if that data is results of a formula?

A few days ago I asked for a formula to auto-calculate a column for ATTRITION
as follows:
col A
row 1 10,000
row 2 8,000
row 3 6,000
row 4
row 5 __________
Attrition 4,000

The formula in cell [A6] is =A1-OFFSET(A1,COUNT(A1:A5)-1,0) confirmed with
Ctrl+Shift+Enter instead of just with Enter.

Now I need to put a simple SUM formula in cells [A1,A2,A3,A4,A5] that sum
columns B,C,D,E across, for example: cell [A1] would contain the formula
SUM(B1:E1), yet still have cell [A6] auto-calculate the same as before. When
I put formulas in cells [A1,A2,A3,A4,A5] the formula in cell [A6] doesn't
function anymore. Does anyone know how to make this work? Thanks