Need formula help

[Ross]

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

 

[Ross]

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

Thanks

 

[JE McGimpsey]

In that case, one way:

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

 

[T. Valko]

Please disregard the zero part.

Yeah, that had me confused!

Ok, try this in B14:

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

 

[Ross]

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?

 

[Ross]

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.

 

[David Biddulph]

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.

 

[T. Valko]

=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

 

[Ross]

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!

 

[T. Valko]

You're welcome. Thanks for the feedback!