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