Nested IF statement
Assume I have the following data:
A1 B1 C1 D1 E1 F1 3 5 4 6 1 2 in cell H1 is the number of columns I want to average. It can be a number from 1 to 6. Assume 3 H1 3 So I have this formula in Cell I1 =IF(H1=1,F1,IF(H1=2,AVERAGE(E1:F1),IF(H1=3,AVERAGE (D1:F1),IF(H1=4,AVERAGE(C1:F1),IF(H1=5,AVERAGE(B1: F1),AVERAGE(A1,F1)))))) Is there an easier way to do this without a nested if? That H1 determines how many columns to average. DG |
Nested IF statement
How's this for simplified?
=) =AVERAGE(OFFSET(F1,0,0,1,-H1)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DG" wrote: Assume I have the following data: A1 B1 C1 D1 E1 F1 3 5 4 6 1 2 in cell H1 is the number of columns I want to average. It can be a number from 1 to 6. Assume 3 H1 3 So I have this formula in Cell I1 =IF(H1=1,F1,IF(H1=2,AVERAGE(E1:F1),IF(H1=3,AVERAGE (D1:F1),IF(H1=4,AVERAGE(C1:F1),IF(H1=5,AVERAGE(B1: F1),AVERAGE(A1,F1)))))) Is there an easier way to do this without a nested if? That H1 determines how many columns to average. DG |
Nested IF statement
One way:
=AVERAGE(OFFSET(F1,,,,-IF(H1="",6,H1))) -- Biff Microsoft Excel MVP "DG" wrote in message ... Assume I have the following data: A1 B1 C1 D1 E1 F1 3 5 4 6 1 2 in cell H1 is the number of columns I want to average. It can be a number from 1 to 6. Assume 3 H1 3 So I have this formula in Cell I1 =IF(H1=1,F1,IF(H1=2,AVERAGE(E1:F1),IF(H1=3,AVERAGE (D1:F1),IF(H1=4,AVERAGE(C1:F1),IF(H1=5,AVERAGE(B1: F1),AVERAGE(A1,F1)))))) Is there an easier way to do this without a nested if? That H1 determines how many columns to average. DG |
Nested IF statement
Thanks, I knew there had to be a better way.
"Luke M" wrote in message ... How's this for simplified? =) =AVERAGE(OFFSET(F1,0,0,1,-H1)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DG" wrote: Assume I have the following data: A1 B1 C1 D1 E1 F1 3 5 4 6 1 2 in cell H1 is the number of columns I want to average. It can be a number from 1 to 6. Assume 3 H1 3 So I have this formula in Cell I1 =IF(H1=1,F1,IF(H1=2,AVERAGE(E1:F1),IF(H1=3,AVERAGE (D1:F1),IF(H1=4,AVERAGE(C1:F1),IF(H1=5,AVERAGE(B1: F1),AVERAGE(A1,F1)))))) Is there an easier way to do this without a nested if? That H1 determines how many columns to average. DG |
All times are GMT +1. The time now is 11:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com