help with shrinking a formula

croweld89 · May 23rd, 2002, 01:16 PM

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(C 6: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

Ian Mac · May 23rd, 2002, 01:27 PM

Quote:

On 2002-05-23 08:16, croweld89 wrote:
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(C 6: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

=INDIRECT("'LCE-IS Mo'!"&VLOOKUP($B$1,{1,"B";2,"C";3,"D";4,"E";5,"F";6,"G";7,"H";8,"I";9,"J";10,"K";11,"L";12,"M"},2,0)&7)

does this help for the first one?

Mark W. · May 23rd, 2002, 01:27 PM

=IF(AND($B$2>=1,$B$2<=12),CHOOSE($B$2,'LCE-IS Mo'!B7,'LCE-IS Mo'!C7,'LCE-IS Mo'!D7,'LCE-IS Mo'!E7,'LCE-IS Mo'!F7,'LCE-IS Mo'!G7,'LCE-IS Mo'!H7,'LCE-IS Mo'!I7,'LCE-IS Mo'!J7,'LCE-IS Mo'!K7,'LCE-IS Mo'!L7,'LCE-IS Mo'!M7),"")

Note: This formulation will work if the 'LCE-IS Mo' worksheet isn't open.

[ This Message was edited by: Mark W. on 2002-05-23 08:31 ]

Ian Mac · May 23rd, 2002, 01:31 PM

=SUM(C6:INDIRECT(VLOOKUP($S$1,{1,"c";2,"d";3,"e";4,"f";5,"g";6,"h"},2,0)&ROW(A6)))

For the second.

croweld89 · May 23rd, 2002, 01:37 PM

Thank you for the great replies. I will get to work on these.

Thanks again,
-Dave

Aladin Akyurek · May 23rd, 2002, 01:57 PM

Q1:

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

Q2:

=SUM(OFFSET($C$6,0,0,1,$B$2))

[ This Message was edited by: Aladin Akyurek on 2002-05-23 08:58 ]

Ian Mac · May 23rd, 2002, 02:01 PM

Quote:

On 2002-05-23 08:57, Aladin Akyurek wrote:
Q1:

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

Q2:

=SUM(OFFSET($C$6,0,0,1,$B$2))

[ This Message was edited by: Aladin Akyurek on 2002-05-23 08:58 ]

Nice

croweld89 · May 23rd, 2002, 05:40 PM

Which is the best formula to nest the offset formula in? Vlookup or Match.

By the way, my formula actually ended up being:

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

Thanks again!
-Dave

croweld89 · May 23rd, 2002, 06:42 PM

Also, I just realized that the row number being set does not allow me to add rows to the LCE-IS page. I'm going to imbed a match function in the offset formula to fix this, but I was wondering if there was something I was missing.

In otherwords, am I making this too hard?
-Dave

Aladin Akyurek · May 23rd, 2002, 07:01 PM

On 2002-05-23 13:42, croweld89 wrote:
Also, I just realized that the row number being set does not allow me to add rows to the LCE-IS page. I'm going to imbed a match function in the offset formula to fix this, but I was wondering if there was something I was missing.

In otherwords, am I making this too hard?

Dave,

OFFSET doesn't require a fixed range. It needs just the base cell ref in your case. So there can be any number of values/rows in the range from the base cell on. So, what do you mean by "the row number being set does not allow me to add rows to the LCE-IS page"?

Aladin

May 23rd, 2002, 01:31 PM	#4
Ian Mac MrExcel MVP Join Date: Feb 2002 Location: Newcastle, UK Posts: 1,174	=SUM(C6:INDIRECT(VLOOKUP($S$1,{1,"c";2,"d";3,"e";4,"f";5,"g";6,"h"},2,0)&ROW(A6))) For the second. __________________ "Have a good time......all the time" Ian Mac

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 23rd, 2002, 01:16 PM	#1
croweld89 Join Date: Mar 2002 Posts: 34	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(C 6: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

May 23rd, 2002, 01:27 PM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,653	=IF(AND($B$2>=1,$B$2<=12),CHOOSE($B$2,'LCE-IS Mo'!B7,'LCE-IS Mo'!C7,'LCE-IS Mo'!D7,'LCE-IS Mo'!E7,'LCE-IS Mo'!F7,'LCE-IS Mo'!G7,'LCE-IS Mo'!H7,'LCE-IS Mo'!I7,'LCE-IS Mo'!J7,'LCE-IS Mo'!K7,'LCE-IS Mo'!L7,'LCE-IS Mo'!M7),"") Note: This formulation will work if the 'LCE-IS Mo' worksheet isn't open. [ This Message was edited by: Mark W. on 2002-05-23 08:31 ]

May 23rd, 2002, 01:37 PM	#5
croweld89 Join Date: Mar 2002 Posts: 34	Thank you for the great replies. I will get to work on these. Thanks again, -Dave

May 23rd, 2002, 01:57 PM	#6
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 42,651	Q1: =OFFSET('LCE-IS Mo'!$B$7,0,$B$2-1,1,1) Q2: =SUM(OFFSET($C$6,0,0,1,$B$2)) [ This Message was edited by: Aladin Akyurek on 2002-05-23 08:58 ]

May 23rd, 2002, 05:40 PM	#8
croweld89 Join Date: Mar 2002 Posts: 34	Which is the best formula to nest the offset formula in? Vlookup or Match. By the way, my formula actually ended up being: =OFFSET('LCE-IS Mo'!A7,0,$B$2,1) Thanks again! -Dave

May 23rd, 2002, 06:42 PM	#9
croweld89 Join Date: Mar 2002 Posts: 34	Also, I just realized that the row number being set does not allow me to add rows to the LCE-IS page. I'm going to imbed a match function in the offset formula to fix this, but I was wondering if there was something I was missing. In otherwords, am I making this too hard? -Dave

May 23rd, 2002, 07:01 PM	#10
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 42,651	On 2002-05-23 13:42, croweld89 wrote: Also, I just realized that the row number being set does not allow me to add rows to the LCE-IS page. I'm going to imbed a match function in the offset formula to fix this, but I was wondering if there was something I was missing. In otherwords, am I making this too hard? Dave, OFFSET doesn't require a fixed range. It needs just the base cell ref in your case. So there can be any number of values/rows in the range from the base cell on. So, what do you mean by "the row number being set does not allow me to add rows to the LCE-IS page"? Aladin