help with shrinking a formula

[croweld89]

I have a formula with 12 nested IF STMTs.

On one monthly summary worksheets, the formula picks up a number from another sheet based on the month. In B2, I place a number 1-12 and obtain detail for the corresponding month.

On monthly trender workdsheets, the formula will sum the corresponding months to the number in B2. If the number in B2 is "5", then the formula sums the numbers in the corresponding row for Jan-May. Sum will also be interchanged with average in the formula.

The formula is as follows:
=IF(OR($B$2=1,$B$2=2,$B$2=3,$B$2=4,$B$2=5,$B$2=6), IF($B$2=1,'LCE-IS Mo'!B7, IF($B$2=2,'LCE-IS Mo'!C7, IF($B$2=3,'LCE-IS Mo'!D7, IF($B$2=4,'LCE-IS Mo'!E7, IF($B$2=5,'LCE-IS Mo'!F7,'LCE-IS Mo'!G7))))), IF(OR($B$2=7, $B$2=8, $B$2=9, $B$2=10,$B$2=11,$B$2=12), IF($B$2=7,'LCE-IS Mo'!H7, IF($B$2=8,'LCE-IS Mo'!I7, IF($B$2=9,'LCE-IS Mo'!J7, IF($B$2=10,'LCE-IS Mo'!K7, IF($B$2=11,'LCE-IS Mo'!L7,'LCE-IS Mo'!M7)))))))

or

=IF(OR($S$1=1,$S$1=2,$S$1=3,$S$1=4,$S$1=5,$S$1=6),IF($S$1=1,C6,IF($S$1=2,SUM(C6:D6),IF($S$1=3,SUM(C6:E6),IF($S$1=4,SUM(C6:F6),IF($S$1=5,SUM(C6:G6),SUM(C6:H6)))))),IF(OR($S$1=7,$S$1=8,$S$1=9,$S$1=10,$S$1=11,$S$1=12),IF($S$1=7,SUM(C6:I6),IF($S$1=8,SUM(C6:J6),IF($S$1=9,SUM(C6:K6),IF($S$1=10,SUM(C6:L6),IF($S$1=11,SUM(C6:M6),SUM(C6:N6))))))))

I'm looking for any suggested formulas that would result in the same results. The formulas work for me, however, as you can see... are very cumbersome.

Thank you in advance..
-Dave

 

[croweld89]

If I change the reference page by adding additional columns, then the "offset" will be incorrect.

ie.
=OFFSET('LCE-IS Mo'!A7,0,$B$2,1)
col. are offset by 0. Therefore, by adding three columns on the reference page, the 0 will need to become a 3.

I guess I had this backwards in my question. I will need to imbed an index formula rather than match.

-Dave

 

[Aladin Akyurek]

On 2002-05-23 14:31, croweld89 wrote:
If I change the reference page by adding additional columns, then the "offset" will be incorrect.

ie.
=OFFSET('LCE-IS Mo'!A7,0,$B$2,1)
col. are offset by 0. Therefore, by adding three columns on the reference page, the 0 will need to become a 3.

I guess I had this backwards in my question. I will need to imbed an index formula rather than match.

-Dave

Maybe I'm missing something:

=OFFSET('LCE-IS Mo'!A7,0,$B$2,1,1)

allows you to insert any number of columns after column A. The value in B2 determines the value to be returned, whose address is ADDRESS(7,B2).

Maybe are you trying to fix A7: Adding 3 rows before A7, will change the base cell to A10.
This message was edited by Aladin Akyurek on 2002-05-24 00:10