I need a formula to recognize the last cell in a column with entered data.
The formula needs to disregard earlier entries in the column. To then take
the last entered value and multiply it by the remaining empty cells in the
given range.
I need a formula to recognize the last cell in a column with entered data.
The formula needs to disregard earlier entries in the column. To then take
the last entered value and multiply it by the remaining empty cells in the
given range.
If you multiply anything by an empty cell, don't you get nothing?
--
HTH
RP
(remove nothere from the email address if mailing direct)
"DAShields" <[email protected]> wrote in message
news:[email protected]...
> I need a formula to recognize the last cell in a column with entered data.
> The formula needs to disregard earlier entries in the column. To then
take
> the last entered value and multiply it by the remaining empty cells in the
> given range.
>
I guess what I meant to ask was: multiply by the number (or count) of
remaining empty cells. Thanks for pointing this mistake out. DAShields
"Bob Phillips" wrote:
> If you multiply anything by an empty cell, don't you get nothing?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "DAShields" <[email protected]> wrote in message
> news:[email protected]...
> > I need a formula to recognize the last cell in a column with entered data.
> > The formula needs to disregard earlier entries in the column. To then
> take
> > the last entered value and multiply it by the remaining empty cells in the
> > given range.
> >
>
>
>
Assuming there are no empty cells prior to the "last"entered
=INDEX(A1:A100,COUNT(A1:A100))*(ROWS(A1:A100)-COUNT(A1:A100))
--
Regards,
Peo Sjoblom
"DAShields" <[email protected]> wrote in message
news:[email protected]...
> I guess what I meant to ask was: multiply by the number (or count) of
> remaining empty cells. Thanks for pointing this mistake out. DAShields
>
> "Bob Phillips" wrote:
>
> > If you multiply anything by an empty cell, don't you get nothing?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "DAShields" <[email protected]> wrote in message
> > news:[email protected]...
> > > I need a formula to recognize the last cell in a column with entered
data.
> > > The formula needs to disregard earlier entries in the column. To then
> > take
> > > the last entered value and multiply it by the remaining empty cells in
the
> > > given range.
> > >
> >
> >
> >
Peo, Thank you for your help. Your formula is almost working, however, I
neglected to ask:
Can we also include the sum of the previously entered cells into the
formula? I'm trying to project a year end number, by using the last value
entered to remain static through the end of the year. I hope this is clear.
Thank you once again.
"Peo Sjoblom" wrote:
> Assuming there are no empty cells prior to the "last"entered
>
> =INDEX(A1:A100,COUNT(A1:A100))*(ROWS(A1:A100)-COUNT(A1:A100))
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
> "DAShields" <[email protected]> wrote in message
> news:[email protected]...
> > I guess what I meant to ask was: multiply by the number (or count) of
> > remaining empty cells. Thanks for pointing this mistake out. DAShields
> >
> > "Bob Phillips" wrote:
> >
> > > If you multiply anything by an empty cell, don't you get nothing?
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "DAShields" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I need a formula to recognize the last cell in a column with entered
> data.
> > > > The formula needs to disregard earlier entries in the column. To then
> > > take
> > > > the last entered value and multiply it by the remaining empty cells in
> the
> > > > given range.
> > > >
> > >
> > >
> > >
>
>
>
Let's say your data is in column A.
This array formula will give you the row number of the last filled cell in
column A:
=MAX(ROW($A$1:$A$10000)*($A$1:$A$10000<>""))
The 10000 is some number that is greater than the last filled row, but less
than 65536. Change it if necessary. You must enter the formula with
CTRL+SHIFT+ENTER. Let's say you put it in B1.
Then this formula will give you the last value from column A:
=INDEX($A:$A,B1)
and this one will give you that final amount multiplied by the count of blank
cells above it:
=INDEX($A:$A,B1)*COUNTBLANK(OFFSET($A$1,0,0,B1,1))
On Thu, 3 Mar 2005 10:27:05 -0800, DAShields
<[email protected]> wrote:
>I need a formula to recognize the last cell in a column with entered data.
>The formula needs to disregard earlier entries in the column. To then take
>the last entered value and multiply it by the remaining empty cells in the
>given range.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks