Nested If Statements

gpotavin

unread,

Jun 10, 2003, 10:01:04 AM6/10/03

to

What is the best work around to Excel's limit of 7 nested
if statements? i.e. =If(A1=1,sum(B2:B25), if(A1=2,sum
(B2:C25))) etc. up through A1=12

In older versions I could nest more than 12 if statements
without any problem but this new functionality is useless.

Any thoughts?

Peo Sjoblom

unread,

Jun 10, 2003, 10:13:57 AM6/10/03

to

One way

=IF(A1="","",SUM(OFFSET($B$2,,,24,$A$1)))

if that was just an example then have a look at help lookup or vlookup

--

Regards,

Peo Sjoblom

"gpotavin" <gpot...@munters.com> wrote in message
news:060601c32f58$bbac88e0$a001...@phx.gbl...

Bruce Girvitz

unread,

Jun 10, 2003, 10:21:49 AM6/10/03

to

The easiest and cleanest way would be to set up a table where column e
starting E1 has values listed 1 thru 12 and column F on the corresponding
row has the values u want to sum. Assuming that the formula is in column G,
formula at G1 is =vlookup(A1,E1:f12,2) Adjust columns or page to suit your
range.

Bruce Girvitz

"gpotavin" <gpot...@munters.com> wrote in message
news:060601c32f58$bbac88e0$a001...@phx.gbl...

Norman Harker

unread,

Jun 10, 2003, 10:20:43 AM6/10/03

to

Hi gpotavin!

I don't think that any version of Excel has had more capability of nesting than
7 nested functions.

One solution to your problem would appear to be:

=SUM(INDIRECT("R2C2:R25C"&(1+A1),FALSE))

I've used INDIRECT to construct the range to be summed based upon the
requirement that you add columns to the range based upon the number in A1. You
might want to vary the formula to cover other possible entries for A1 or you may
data validate the A1 entry.

But in general terms the better solution to multiple IF requirements will be to
use VLOOKUP.

--
Regards
Norman Harker MVP (Excel)
Tuesday: Public Holidays: Jordan (Army Day), Portugal (National Day).
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au

"gpotavin" <gpot...@munters.com> wrote in message
news:060601c32f58$bbac88e0$a001...@phx.gbl...

Anon

unread,

Jun 10, 2003, 10:21:11 AM6/10/03

to

"gpotavin" <gpot...@munters.com> wrote in message
news:060601c32f58$bbac88e0$a001...@phx.gbl...

It's best not to use a workaround at all. A statement with 7 IFs is
virtually unreadable anyway. Why not do it properly? In the case you cite,
where you are presumably wanting to sum rows 2 to 25 over the number of
columns given by A1, you could use
=SUM(B2:OFFSET(B25,0,A1-1))
Or, to limit to 12 columns maximum,
=SUM(B2:OFFSET(B25,0,MIN(12,A1-1)))

Mark Graesser

unread,

Jun 10, 2003, 11:51:47 AM6/10/03

to

Usually the best technique for this need is VLOOKUP. However, if you are
set on using IF statements you can get around the 7 limit by not nesting.
Example:

Text:
=IF(condition1,truevalue1,"")&IF(condition2,truevalue2,"")&IF(condition3,tru
evalue3,"")&...

Numbers:
=IF(condition1,truevalue1,0)+IF(condition2,truevalue2,0)+IF(condition3,truev
alue3,0)+...

Since the IF statements aren't nested there is no limit. You will run into
trouble if a value will make more then one IF statement true. This can
usually be gotten around by using AND and OR statements in your conditions.

Good Luck,
Mark Graesser

"gpotavin" <gpot...@munters.com> wrote in message
news:060601c32f58$bbac88e0$a001...@phx.gbl...