Restrictions on the IF function in Excel (nested queries)

[EH Chew]

Hi

Is there a way I can overcome the limitation of Excel in
IF function nested queries? (If I am not mistaken, If
function can only be nested max 7 times)

The situation is below:
If mth = 1, sum (A1)
If mth = 2, sum (A1:B1)
If mth = 3, sum (A1:C1)
If mth = 4, sum (D1)
If mth = 5, sum (D1:E1)
If mth = 6, sum (D1:F1)
If mth = 7, sum (G1)
If mth = 8, sum (G1:H1)
If mth = 9, sum (G1:I1)
If mth = 10, sum (J1)
If mth = 11, sum (J1:K1)
If mth = 12, sum (J1:L1)

I would appreciate if someone cld assist me in this.

Thanks in advance for your help.

 

[RagDyer]

One way -
If the month number is in A10, then:

=SUM(CHOOSE(A10,A1,A1:B1,A1:C1,D1,D1:E1,D1:F1,G1,G1:H1,G1:I1,J1,J1:K1,J1:L1)
)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Hi

Is there a way I can overcome the limitation of Excel in
IF function nested queries? (If I am not mistaken, If
function can only be nested max 7 times)

The situation is below:
If mth = 1, sum (A1)
If mth = 2, sum (A1:B1)
If mth = 3, sum (A1:C1)
If mth = 4, sum (D1)
If mth = 5, sum (D1:E1)
If mth = 6, sum (D1:F1)
If mth = 7, sum (G1)
If mth = 8, sum (G1:H1)
If mth = 9, sum (G1:I1)
If mth = 10, sum (J1)
If mth = 11, sum (J1:K1)
If mth = 12, sum (J1:L1)

I would appreciate if someone cld assist me in this.

Thanks in advance for your help.

 

[Paul]

Hi

Is there a way I can overcome the limitation of Excel in
IF function nested queries? (If I am not mistaken, If
function can only be nested max 7 times)

The situation is below:
If mth = 1, sum (A1)
If mth = 2, sum (A1:B1)
If mth = 3, sum (A1:C1)
If mth = 4, sum (D1)
If mth = 5, sum (D1:E1)
If mth = 6, sum (D1:F1)
If mth = 7, sum (G1)
If mth = 8, sum (G1:H1)
If mth = 9, sum (G1:I1)
If mth = 10, sum (J1)
If mth = 11, sum (J1:K1)
If mth = 12, sum (J1:L1)

I would appreciate if someone cld assist me in this.

Thanks in advance for your help.

Suppose the month number is in A2. Then you could use this formula:
=SUM(OFFSET(A1,0,INT((A2-1)/3)*3,1,MOD(A2-1,3)+1))

 

[Ron Rosenfeld]

Hi

Is there a way I can overcome the limitation of Excel in
IF function nested queries? (If I am not mistaken, If
function can only be nested max 7 times)

The situation is below:
If mth = 1, sum (A1)
If mth = 2, sum (A1:B1)
If mth = 3, sum (A1:C1)
If mth = 4, sum (D1)
If mth = 5, sum (D1:E1)
If mth = 6, sum (D1:F1)
If mth = 7, sum (G1)
If mth = 8, sum (G1:H1)
If mth = 9, sum (G1:I1)
If mth = 10, sum (J1)
If mth = 11, sum (J1:K1)
If mth = 12, sum (J1:L1)

I would appreciate if someone cld assist me in this.

Thanks in advance for your help.

One way is to make use of the ADDRESS and INDIRECT functions to compute the
range of interest:

=SUM(INDIRECT(ADDRESS(1,INT((mth-1)/3)*3+1)&":"&ADDRESS(1,mth)))


--ron