Exceeding Limit on Nested IF Statements

[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?

 

[Martin Fishlock]

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.

 

[Martin Fishlock]

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.

 

[rwohlner]

Tyro:

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

Thanks.

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

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?