Exceeding Limit on Nested IF Statements

  • Thread starter Thread starter rwohlner
  • Start date Start date
R

rwohlner

I am converting a budget spreadsheet from Lotus 1-2-3 to Excel. Excel's
limit on nested IF statements is creating my biggest challenge. I have
budget and actual columns next to one another for each month. A Lotus
formula reads from a cell identifying the current month that requires 12 if
statements. [i.e. (If month = January, add one column, if february add two
columns, ...if December add 12 columns.)] What is my Excel alternative to
nested if's in this situation?
 
Who could say? Show us your data, then we can provide you with an Excel
formula. You might find that IF is not involved, at all, in Excel. With a
data input example and a resultant answer, we can help.

Tyro
 
Hi,

One solution is to use an indirect function:

=SUM(INDIRECT("R5C2:R5C"&$B$7+1,FALSE))

In this example the data is in row 5 and starts at column b (R5C2) and it
continues for the next n columns as shown by B7 which holds 1 for Jan, 2 for
Feb etc.

you need to adjust the b7 value by the indented columns here one column, so
plus one.

This solution requires that you have a separate sheet linked to source data
to get the results with twelve consecutive columns of data.
 
Hi,

One solution is to use an indirect function:

=SUM(INDIRECT("R5C2:R5C"&$B$7+1,FALSE))

In this example the data is in row 5 and starts at column b (R5C2) and it
continues for the next n columns as shown by B7 which holds 1 for Jan, 2 for
Feb etc.

you need to adjust the b7 value by the indented columns here one column, so
plus one.

This solution requires that you have a separate sheet linked to source data
to get the results with twelve consecutive columns of data.
 
Tyro:

Please walk me through how I can show you a sample.

Thanks.


Tyro said:
Who could say? Show us your data, then we can provide you with an Excel
formula. You might find that IF is not involved, at all, in Excel. With a
data input example and a resultant answer, we can help.

Tyro

rwohlner said:
I am converting a budget spreadsheet from Lotus 1-2-3 to Excel. Excel's
limit on nested IF statements is creating my biggest challenge. I have
budget and actual columns next to one another for each month. A Lotus
formula reads from a cell identifying the current month that requires 12
if
statements. [i.e. (If month = January, add one column, if february add
two
columns, ...if December add 12 columns.)] What is my Excel alternative to
nested if's in this situation?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding variable number of columns 8
Nested IF statements 14
Formula nesting with IF statements 2
Monthly Change Using Nested IFs? 4
Help with Nested IF Statements 4
if function (if more that 7 nesting) 2
Nested if statements 2
What function(s) to use? 1

Back
Top