Formula LOOKUP(9.999E+307,C65:O65)

[jevi]

Hi all,

I know this old formula long time ago =LOOKUP(9.999E+307,C65:O65)-F65

I have 12 columns for the 12 months from January to Dicember and then I have a column when I do the Difference between last month and the previous month. So in order not to change every months the cells I came up with this formula which LOOKUP(9.999E+307,C65:O65) gaves me the last month value entered in column but I don't know how to do auotmatically put then the previous month (F65).

So if I have put the date of April I would like to have April - March and so on for the next Month May (LOOKUP(9.999E+307,C65:O65 - this formula will give me the data of May) - April (but I don't know how to say the previous month in the formula).

I hope I was clear enough.

Thank you,

 

[JoeMo]

Assuming you fill all cells up to and including the most recent month, and the months beyond that are empty, you could do it this way:

Book2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Diff Last 2
2	87	83	17	59	70								11
Sheet6

Cell Formulas
Range	Formula
M2	M2	=INDEX($A$1:$L$2,2,COUNT($A$2:$L$2))-INDEX($A$1:$L$2,2,COUNT($A$2:$L$2)-1)

 

[jevi]

It worked great just to make sure I did understand it well so if I want to continue as I have other rows of data as I have branches in different rows with data so it woul be
M3= INDEX($A$1:$L$3,3,COUNT($A$3:$L$3))-INDEX($A$1:$L$3,3,COUNT($A$3:$L$3)-1)
M4= INDEX($A$2:$L$4,3,COUNT($A$4:$L$4))-INDEX($A$2:$L$4,3,COUNT($A$4:$L$4)-1)
and so on......and it works but I don't understand why it works with 3 as row and not if I use 4 or etc...it works only with 3. Maybe it is too late .

Thank you so much.

 

[JoeMo]

You are welcome - thanks for the reply. I didn't know you have more than 1 row of data. In that case it will be easier to enter this formula in M2 and then just copy it down to cover your additional rows.

=INDEX($A2:$L2,1,COUNT($A2:$L2))-INDEX($A2:$L22,1,COUNT($A2:$L2)-1)

 

[shaowu459]

Hi, JoeMo's formula will work well for your case, but please change L22 to L2 in the second INDEX.
Here is another formula just for your reference in case there is no blank cells for any months.

MrExcel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Diff Last 2
2	87	83	17	59	70								11
3	87	83	17	59	70	84							14
Sheet3

Cell Formulas
Range	Formula
M2:M3	M2	=SUM(INDEX(A2:L2,COUNTA(A2:L2)-{0,1})*{1,-1})

 

[jevi]

Thank you JoeMo, it worked as a treat and sorry it was my fault that I did't tell I had other rows.

I will keep that in mind Shaowu so I wrote it down in my file as for sure I will need it someday. I love formulas but I am not that good as you and JoeMo so is beautiful when you see what you can do with them and how much time and boring work I can save.

 

[Peter_SSs]

for the next Month May (LOOKUP(9.999E+307,C65:O65 - this formula will give me the data of May) - April (but I don't know how to say the previous month in the formula).

If you wanted to continue with that LOOKUP(9.99... function, you could try this also

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

20 06 17.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Diff Last 2
2	87	83	17	59	70								11
3	87	83	17	59	70	84							14
Sheet2 (2)

Cell Formulas
Range	Formula
M2:M3	M2	=LOOKUP(9.999E+307,B2:L2)-LOOKUP(9.999E+307,B2:L2,A2:K2)

 

[jevi]

Thank you Peter for the formula, really great to know all the options .

I did update my information. Thank you for the advice.

 

[Peter_SSs]

You're welcome, and thanks for updating your profile.