Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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. |
#2
![]() |
|||
|
|||
![]()
__________________
I am not human. I am an Excel Wizard |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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. |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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. |
#5
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Martin and Bob,
Thank you both for your help. Huaming |
#6
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Referencing relative to a formula reference | Excel Worksheet Functions | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
Formula needs to reference anchored relative cell | Excel Programming | |||
Using Relative Reference in a Formula | Excel Programming | |||
Mixing Absolute and Relative Reference in a Formula | Excel Programming |