How to Use Relative Reference in Array Formula
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. |
Answer: How to Use Relative Reference in Array Formula
|
How to Use Relative Reference in Array Formula
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. |
How to Use Relative Reference in Array Formula
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. |
How to Use Relative Reference in Array Formula
Martin and Bob,
Thank you both for your help. Huaming |
How to Use Relative Reference in Array Formula
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. |
All times are GMT +1. The time now is 06:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com