Thread: Calculation II

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

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" <Mike@discussions.microsoft.com> wrote in message
news:C7087D01-C75B-4E72-88E6-586CCC341642@microsoft.com...
> 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

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?

"Mike" wrote:

> 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

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)


"Mike" <Mike@discussions.microsoft.com> wrote in message
news:5D22D52F-D138-4819-A9F8-D31408D69F31@microsoft.com...
> 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?
>
> "Mike" wrote:
>
> > 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