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

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

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

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