Dealing with blank cells

[Victoria]

hi again - I hope it's OK to ask 2 questions on the same day! This problem
is related to the one I posed earlier, but more general.

Let's say a sheet has 3 columns. Columns A and B contain numbers, but there
are also varying numbers of blank cells. I'm looking for a formula I can
drag down in column C that sums the B value (if it exists) and the first A
value encountered on the same row or above. Here is a sample.

A B C
6 1 7
7 13

3 9
8
4 12

2 10
7 3 10

What drag-down formula can I use in Column C?
I'm really knew at this, and I'm grateful for any clues.
Victoria

 

[Jacob Skaria]

Hi Victoria

<<I hope it's OK to ask 2 questions on the same day!

You are most welcome.


<<I'm looking for a formula I can drag down in column C that sums the
<<B value (if it exists) and the first A value

Try the below formula in C1 and copy down.
=IF(B1,LOOKUP(10^10,A$1:A1)+B1,"")

If this post helps click Yes

 

[Victoria]

Thanks Jacob - this works perfectly! But.... I'm not sure why??? Consider,
for example, the formula ...

=IF(B9,LOOKUP(10^10,A$1:A9)+B9,"")

The Excel help says that if the lookup_value (10^10) cannot be found in the
stated range, then it uses the largest value less than or equal to the
lookup_value. so, why does your function correctly pick the A value 7, even
though it isn't the largest value in A$1:A9?

=IF(B9,LOOKUP(10^10,A$1:A9)+B9,"")

 

[Jacob Skaria]

That applies true for a sorted range. Here since 10^10 cannot be found it
picks up the last value in range

If this post helps click Yes

 

[Victoria]

You ROCK!

That applies true for a sorted range. Here since 10^10 cannot be found it
picks up the last value in range

If this post helps click Yes