Hello, Experts,

I wanted to make array formula in multiple cells with relative
references, such as:

In cell C1: {=SUM(LEN(A1:A5))}
In cell C2: {=SUM(LEN(A2:A6))}
In cell C3: {=SUM(LEN(A3:A7))}
In cell C4: {=SUM(LEN(A4:A8))}
In cell C5: {=SUM(LEN(A5:A9))}
....
....
....

but when I highlited range C1:C5 and enter the array by presss
Control/Shift/Enter, the array formula in these cells became
{=SUM(LEN(A1:A5))}. Is there some way to force the array formula to
adopt relative reference?

thanks.

1. Select the range of cells where you want to enter the array formula (in this case, C1:C5).
2. Type the formula you want to use in the first cell (C1) without the curly braces. In this case, it would be =SUM(LEN(A1:A5)).
3. Instead of pressing Enter, press Control + Enter. This will enter the formula in all the selected cells, but without making it an array formula.
4. Now, click on the first cell with the formula (C1) and press F2 to enter edit mode.
5. Change the cell references in the formula to be relative references. In this case, change A1:A5 to A2:A6.
6. Press Control + Enter to confirm the formula. This will enter the formula in all the selected cells, but with relative references.

You put the array formula in cell c1
pressing ctrl+shft+enter
and then copy down

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


" wrote:

Hello, Experts,

I wanted to make array formula in multiple cells with relative
references, such as:

In cell C1: {=SUM(LEN(A1:A5))}
In cell C2: {=SUM(LEN(A2:A6))}
In cell C3: {=SUM(LEN(A3:A7))}
In cell C4: {=SUM(LEN(A4:A8))}
In cell C5: {=SUM(LEN(A5:A9))}
....
....
....

but when I highlited range C1:C5 and enter the array by presss
Control/Shift/Enter, the array formula in these cells became
{=SUM(LEN(A1:A5))}. Is there some way to force the array formula to
adopt relative reference?

thanks.

Don't select C1:C5 and enter the formula, just select C1 and array enter it,
then copy down.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


wrote in message
ps.com...
Hello, Experts,

I wanted to make array formula in multiple cells with relative
references, such as:

In cell C1: {=SUM(LEN(A1:A5))}
In cell C2: {=SUM(LEN(A2:A6))}
In cell C3: {=SUM(LEN(A3:A7))}
In cell C4: {=SUM(LEN(A4:A8))}
In cell C5: {=SUM(LEN(A5:A9))}
...
...
...

but when I highlited range C1:C5 and enter the array by presss
Control/Shift/Enter, the array formula in these cells became
{=SUM(LEN(A1:A5))}. Is there some way to force the array formula to
adopt relative reference?

thanks.

Martin and Bob,
Thank you both for your help.
Huaming

To avoid having to "array enter" the formula, replace SUM by SUMPRODUCT

--
Regards,
Luc.

"Festina Lente"


" wrote:

Hello, Experts,

I wanted to make array formula in multiple cells with relative
references, such as:

In cell C1: {=SUM(LEN(A1:A5))}
In cell C2: {=SUM(LEN(A2:A6))}
In cell C3: {=SUM(LEN(A3:A7))}
In cell C4: {=SUM(LEN(A4:A8))}
In cell C5: {=SUM(LEN(A5:A9))}
....
....
....

but when I highlited range C1:C5 and enter the array by presss
Control/Shift/Enter, the array formula in these cells became
{=SUM(LEN(A1:A5))}. Is there some way to force the array formula to
adopt relative reference?

thanks.