help with shrinking a formula

croweld89

New Member
Joined
Mar 5, 2002
Messages
33
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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(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



=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?
 
Upvote 0
=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
 
Upvote 0
=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.
 
Upvote 0
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
 
Upvote 0
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 :biggrin:
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

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

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top