Last value & if,then

[Guest]

Example

A B C
7 4 =$A$1*B1
5 =$A$1*B2
6 =$A$1*B3
3 1 =$A$4*B4
4 =$A$4*B5

Isn't there a way to tell Excel to always multiply A*B, without using the
$A$? The reason I ask is we have hundreds of sections with new A values and
I don't want to put a new $$ formula in for each section. I was thinking of
something along the lines of A*B, if A=blank use last A value- but I can't
find any examples of this.

 

[Guest]

Can you add a (hidden) helper column? That column would do essentially just
what you said: pick up the new value in column A or continue the old value
is column A is blank. In C1, =A1. In C2, =if(isblank(a2),c1,a2). In D1,
=B1*C1 and in D2, =B2*C2. Then just copy down the formulas in C22 to
complete your table.
--Bruce

 

[Guest]

One way

=B1*INDEX($A$1:A1,MAX(ISNUMBER($A$1:A1)*ROW($A$1:A1)))

After you type it in, hit Control+Shift+Enter instead of just Enter as this
is an array formula. Then copy down column C

 

[Guest]

Hey- thanks a lot!
= )

One way

=B1*INDEX($A$1:A1,MAX(ISNUMBER($A$1:A1)*ROW($A$1:A1)))

After you type it in, hit Control+Shift+Enter instead of just Enter as this
is an array formula. Then copy down column C

 

[Guest]

you tell this to C2:
=IF(A2="",C1/B1*B2,A2*B2)

then drag em all d way down...

 

[Guest]

You're welcome. In retrospect, I would suggest using this slightly modified
version. The last one will result in an error if your table does not begin
in Row 1 (so if you move it or insert a row in cell A1 - the last formula
will result in error whereas this one would not). Sorry for the confusion.


=B1*INDEX($A$1:A1,MAX(ISNUMBER($A$1:A1)*(ROW(INDIRECT("1:"&ROWS($A$1:A1))))))

 

[Biff]

Hi!

Try this:

=LOOKUP(9.999999999999E+307,A$1:A1)*B1

Copy down as needed.

Biff

 

[Aladin Akyurek]

Insert a row before the current data and enter a 0 in A1.

In C2 enter & copy down:

=LOOKUP(9.99999999999999E+307,$A$1:A2)*B2

 

[Guest]

Hi Biff- This worked great!

Thanks!
= )
Angie

Hi!

Try this:

=LOOKUP(9.999999999999E+307,A$1:A1)*B1

Copy down as needed.

Biff