Array formula

[Jeff]

Hello,

I am using this formula to multiply all the digits in an integer:

=PRODUCT(VALUE(MID(C55,ROW(A1:OFFSET(A1,LEN(C55)-1,0)),1)))

So far so good...

But I want to copy this array formula for a large range of cells, each
with a different integer. But my understanding is that an array
formula must be the same for the range of cells. How can I get it to
update to different cells (C55 in the above formula, the next one is
C64, e.g.) like a regular formula?

Thanks,
Jeff

 

[Guest]

Just drag down (or copy) like a normal formula, Excel shall update relative
references alike in case of regular formulae.

Regards,
Stefi

„Jeff†ezt írta:

 

[Pete_UK]

Have you tried a straight copy and paste to another cell? Doesn't the
C55 change relative to where you have copied it to?

Pete

 

[Jeff]

Just drag down (or copy) like a normal formula, Excel shall update relative
references alike in case of regular formulae.

Regards,
Stefi

,,Jeff" ezt írta:

Stefi and Pete,

Thanks for helping me out here, but cut and paste doesn't work in this
case. All I get is the #Value in all of the cells or exactly the same
value in every cell. Any other ideas?

 

[Peo Sjoblom]

Which part of the formula do you want to copy, if you want to copy and have
the formula to change to

=PRODUCT(VALUE(MID(C56,ROW(A2:OFFSET(A2,LEN(C56)-1,0)),1)))

when you copy it down one row then you can just drag the formula using the
fill handle, if you want parts of the formula to stay the same like

=PRODUCT(VALUE(MID(C56,ROW($A$1:OFFSET($A$1,LEN(C56)-1,0)),1)))

so C55 changes to C56 but A1 stays the same then you need to use the dollar
signs as I did then use the fillhandle and copy

If you get a value error it means that you are trying to calculate a cell
with text

=VALUE(A2)

with a in A2 will return value. Normally you use VALUE to force text numbers
into real numbers. Finally if the formula stays the same then you can do
tools>options>calculation and make sure the calculation is set to automatic.
Also make sure the formatting is not text


--

Regards,

Peo Sjoblom






Stefi and Pete,

Thanks for helping me out here, but cut and paste doesn't work in this
case. All I get is the #Value in all of the cells or exactly the same
value in every cell. Any other ideas?