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

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

In that case, one way:

=INDEX(B1:B12,COUNT(B1:B12))

In article ,
Ross wrote:

Sorry folks. Please disregard the zero part. I was thinking about another
part of the spreadsheet.

Thanks

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

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

Thanks for the quick response! This formula worked pretty well except for if
a month was skipped, then it would lag behind a month when the next months
input was made. I ended up being able to use the formula that JE McGimpsey
provided as it handled the blanks a little better.
--
smither fan


"JE McGimpsey" wrote:

In that case, one way:

=INDEX(B1:B12,COUNT(B1:B12))

In article ,
Ross wrote:

Sorry folks. Please disregard the zero part. I was thinking about another
part of the spreadsheet.

Thanks

IF, COUNT, and LOOKUP are all standard Excel functions, so (along with every
other Excel function but one) if you look them up in Excel help it will tell
you the syntax, give examples, and probably give "See also" links to related
functions.
--
David Biddulph

"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

=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

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

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