Variable Lookup based on another formula

[gaftalik]

Hi everybody ,

I need to return the months names of V4 (the last 2 months in this case ) of index J7:U7, is that possible ?

The answer should be November & December.

Many thanks .

question abt average with index.xls
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
4												Averagelast	2	months
5													5
6													12
7	October				November				December
8	U	T	TOTAL		U	T	TOTAL
9	318	115	433	49%	339	112	451	50%	211	240	522	69%	487
10	298	144	442	51%	316	133	449	50%	122	110	240	31%	345
11			875				900				762
Sheet1

 

[IML]

Not sure if this is what your after, but you could try
="Average of "&TEXT(EDATE($R$7&0,-V1+1),"mmm-")&TEXT($R$7&0,"mmm")

this requires the tool pak be activated and assumes R7 is text as opposed to a date

 

[just_jon]

Only going to be these 3 cells? Or is 4-12 possible.

 

[Aladin Akyurek]

You have month names in merged cells. Assuming that each month name is actually in the cell above the the cell of U-field...

In W5 enter & copy down:

=IF(ROW()-ROW($W$5)+1<=$V$4,INDEX($J$7:$U$7,($V$6-($V$4-(COUNTA($W$4:W4)-1))*4)+1),"")

You need to test this though with V4 set to different values.

 

[gaftalik]

Champ !, i tried it , it worked as it is but it returned only November instead of November & December .

Shall i guess that since it shall return the last 2 months and November is the first that means what shall come after is definitely december or a tuning in the formula could be done ?

Dear IML ur formula returned an error!?
Dear Just_Jon , yes it could be more than 3 cells

Thank you all for your concern .

 

[just_jon]

I think, in order for the problem to have an optimum [ == Aladin ] solution, you'd have to let us know what the largest possible range of interest might be...

 

[gaftalik]

Not sure if this is what your after, but you could try
="Average of "&TEXT(EDATE($R$7&0,-V1+1),"mmm-")&TEXT($R$7&0,"mmm")

this requires the tool pak be activated and assumes R7 is text as opposed to a date

Sorry , when i put the dates as true dates, the formula returned
"Average of feb-mar" where as it considere the 2nd month and the 3rd month of the year not from the index i have (J7:U7)

question abt average with index.xls
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
4										Averagelast			2	months
5
6
7	oct.03				nov.03				dec.03
8	U	T	TOTAL		U	T	TOTAL
9	318	115	433	49%	339	112	451	50%	211	240	522	69%	469	Averageoffeb-mar
10	298	144	442	51%	316	133	449	50%	122	110	240	31%	377
11			875				900				762
Sheet2

 

[Aladin Akyurek]

...i tried it , it worked as it is but it returned only November instead of November & December .

Shall i guess that since it shall return the last 2 months and November is the first that means what shall come after is definitely december or a tuning in the formula could be done ?...

Month names should be in the first cell of the merged cells in row 7...

aaReturnNameLastNmonths gaftalik.xls
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
4											Averagelast		2	months
5														November
6														December
7	October				November				December
8	U	T	TOTAL		U	T	TOTAL
9	318	115			339	112			211	240	522
10	298	144			316	133			122	110	240
11
Sheet1

the formula in W5, which is copied down, is:

=IF(ROW()-ROW($W$5)+1<=$V$4,INDEX($J$7:$U$7,($V$6-($V$4-(COUNTA($W$4:W4)-1))*4)+1),"")

The ROW($W$5) anchors the formula to the first formula cell which is W5.

 

[gaftalik]

I got it, both formulas worked as in the following table

question abt average with index.xls
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
4										Averagelast			2	months
5													5	nov
6													12	dec
7	oct				nov				dec
8	U	T	TOTAL		U	T	TOTAL
9	318	115	433	49%	339	112	451	50%	211	240	522	69%	487	Averageofnov-dec
10	298	144	442	51%	316	133	449	50%	122	110	240	31%	345
11			875				900				762
Sheet2

 

[gaftalik]

Once i turned the dates to true dates such in the table,please check , since i need also to retrieve the nbr of days of those months ...i have to apply the formula of Mr Aladdin.

question abt average with index.xls
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
4										Averagelast			2	months
5													5	nov.03
6													12	dec.03
7	oct.03				nov.03				dec.03
8	U	T	TOTAL		U	T	TOTAL
9	318	115	433	49%	339	112	451	50%	211	240	522	69%	487	Averageoffeb-mar
10	298	144	442	51%	316	133	449	50%	122	110	240	31%	345
11			875				900				762
Sheet2

Thank you both for the answers and for your attention .