Array formula

  • Thread starter Thread starter Jeff
  • Start date Start date
J

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
 
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:
 
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
 
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:
Click to expand...

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?
 
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?
 
Back
Top