Restrictions on the IF function in Excel (nested queries)

  • Thread starter Thread starter EH Chew
  • Start date Start date
E

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.
 
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.
 
EH Chew said:
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.
Click to expand...

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))
 
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.
Click to expand...

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
 
Back
Top