MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Clustering Of IF Functions


Posted by Mr Jolly on September 25, 2001 6:13 AM

I am trying to write a formula which will calculate differently, depending on what month it is.

i.e.

if(A1=Jan,sum(b:b),if(a1=feb,sum(c:c)...........

that sort of thing

but excel wont let me cluster more than 8 (sep)

any other ideas would be excellent

thanks

Mark


Posted by Aladin Akyurek on September 25, 2001 6:38 AM

Mark --

Try:

=SUM(INDIRECT(VLOOKUP(A1,{"Jan","B";"Feb","C";"Mar","D";"Apr","E";"May","F";"Jun","G";"Jul","H";"Aug","I";"Sep","J";"Oct","K";"Nov","L";"Dec","M"},2,0)&":"&VLOOKUP(A1,{"Jan","B";"Feb","C";"Mar","D";"Apr","E";"May","F";"Jun","G";"Jul","H";"Aug","I";"Sep","J";"Oct","K";"Nov","L";"Dec","M"},2,0)))

In most cases, long chain of (nested) IFs can be transformed into a case for which VLOOKUP holds.

Aladin

============

Posted by mr jolly on September 25, 2001 7:04 AM

aladin

thanks

Mark

Posted by Juan Pablo on September 25, 2001 7:18 AM

Aladin, one question, why 2 VLOOKUPs ?

Why can't you replace the two VLOOKUPs with just one ?, i mean instead of doing VLOOKUP(...) & ":" VLOOKUP(...), just do VLOOKUP(A1,{"Jan","B:B";"Feb","C:C",....},2,0) ?

Juan Pablo

Posted by Aladin Akyurek on September 25, 2001 8:11 AM

Good question...

Juan. One VLOOKUP will do indeed:

=SUM(INDIRECT(VLOOKUP(A1,{"Jan","B:B";"Feb","C:C";"Mar","D:D";"Apr","E:E";"May","F:F";"Jun","G:G";"Jul","H:H";"Aug","I:I";"Sep","J:J";"Oct","K:K";"Nov","L:L";"Dec","M:M"},2,0)))

Thanks pointing this out.

Aladin