Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exceeding Limit on Nested IF Statements
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exceeding Limit on Nested IF Statements
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" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exceeding Limit on Nested IF Statements
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. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "rwohlner" wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exceeding Limit on Nested IF Statements
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. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "rwohlner" wrote: 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exceeding Limit on Nested IF Statements
Tyro:
Please walk me through how I can show you a sample. Thanks. "Tyro" wrote: 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" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested If Exceeding 7 Calculations | Excel Discussion (Misc queries) | |||
exceeding columns limit | Excel Discussion (Misc queries) | |||
Exceeding the limit for Nested IFs | Excel Worksheet Functions | |||
How to add more than 7 if nested statements. 7 is the limit. | Excel Discussion (Misc queries) | |||
Employing constant arrays to limit nested IF statements. | Excel Worksheet Functions |