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