You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Ross" wrote in message
...
Thanks for everything! I'm not a 'techie', so I can tell you it's been
difficult to understand MS's help alot of the time!! I'm sure I would
have
been left scratching my head on at least some part it, but your
explanation
is very clear. Also, when I replied to JE's response, I mean't to
reference
your formula, but made a mistake. Sorry for that one!
--
smither fan


"T. Valko" wrote:

=IF(COUNT(B1:B12),LOOKUP(1E100,B1:B12),"")

Ok, the first thing we're doing is testing the range to make sure there
is
at least one number entered.

=IF(COUNT(B1:B12)

COUNT returns the count (oddly enough!) of numbers in the range. If COUNT
returns any number other than 0 then the logical test of the IF function
evaluates as TRUE and proceeds to evaluate this portion:
LOOKUP(1E100,B1:B12). If there were no numbers in the range then this
portion would return an error: LOOKUP(1E100,B1:B12). So we use COUNT as
an
error trap to prevent that from happening. If there are no numbers in the
range then COUNT returns a 0 and the logical test of the IF function
evaluates to FALSE then proceeds to evaluate this portion: "". This is an
empty TEXT string which leaves the cell looking blank. This is usually
more
desireable than seeing errors.

OK, let's assume there are numbers entered in the range. You want to find
the last number entered. This portion of the formula does that:
LOOKUP(1E100,B1:B12).

How it does that (this is kind of confusing at first and may need to be
read
a couple of times!):

If the lookup_value is greater than every value in the range the formula
returns the *last* value in the range that is less than the lookup_value.
To
ensure that every value in the range is less than the lookup_value so we
can
get the last value in the range we use an arbitrary lookup_value that is
guaranteed to be greater than any value in the range.

The arbitrary lookup_value I used is 1E100 which is scientific notation
for
a very large number. 1E100 = 1 followed by 100 zeros. That is one huge
number and there's a pretty good chance that 1E100 is greater than every
value in the range. Since 1E100 is greater than every value in the range
LOOKUP(1E100,B1:B12) returns the *last* numeric value entered in the
range.


exp101
--
Biff
Microsoft Excel MVP


"Ross" wrote in message
...
Thanks much! This formula really works well. It even works if you
skip a
month, or input a zero. Could you please explain the formula for me?
--
smither fan


"T. Valko" wrote:

Please disregard the zero part.

Yeah, that had me confused!

Ok, try this in B14:

=IF(COUNT(B1:B12),LOOKUP(1E100,B1:B12),"")

--
Biff
Microsoft Excel MVP


"Ross" wrote in message
...
Sorry folks. Please disregard the zero part. I was thinking about
another
part of the spreadsheet.

Thanks

--
smither fan


"Ross" wrote:

Hi all
I have a worksheet with A1:A12 as Jan thru Dec and B1:B12 as
monthly
totals.
I would like to put a formula in B14 that would reflect the new
months
total
as I input it. In other words, it would show January's total until
I
input
February's, and so forth? Also, It should show a zero in the
months
that
I
don't input anything into. Can I do such a thing?

Many thanks, and I do leave feedback, as I hope others do...
--
smither fan