calculating from last inputted cell

D

DAShields

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.
 
B

Bob Phillips

If you multiply anything by an empty cell, don't you get nothing?

--

HTH

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

DAShields

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
 
P

Peo Sjoblom

Assuming there are no empty cells prior to the "last"entered

=INDEX(A1:A100,COUNT(A1:A100))*(ROWS(A1:A100)-COUNT(A1:A100))
 
D

DAShields

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.
 
M

Myrna Larson

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))
 
Top