Return value based on previous month

[johnswaine]

Hi,
I have a table that is a1:a13 Jan - Dec, then below each month are values, I want to add a column for each row to show the value for the previous month from today, so if its October today then return each value for September, then I can create a chart based on the previous month when viewing.

Thanks in advance

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Previous Month 1 1 1 1 1 1 1 1 10 1 1 1 10 1 1 1 1 1 1 1 1 10 1 1 1 10 1 1 1 1 1 1 1 1 10 1 1 1 10 <tbody> </tbody>
							450	450	450	450	450	-

<colgroup><col><col span="11"><col></colgroup><tbody>
</tbody>

 

[Momentman]

Am assuming that the headers are Text and not dates formatted as Text.

The below should work, but not for jan

Book1
	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	Previous Month
2	1	1	1	1	1	1	1	1	10	1	1	1	10
3	1	1	1	1	1	1	1	1	10	1	1	1	10
4	1	1	1	1	1	1	1	1	10	1	1	1	10
Sheet11

Cell Formulas
Range	Formula
M2	=INDEX($A2:$L2,MATCH(TEXT(TODAY(),"mmm"),$A$1:$L$1,0)-1)

 

[AlanY]

try

Book1
	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	Previous Month
2	1	1	1	1	1	1	1	1	10	1	1	1	10
3	1	1	1	1	1	1	1	1	10	1	1	1	10
4	1	1	1	1	1	1	1	1	10	1	1	1	10
Sheet2

Cell Formulas
Range	Formula
M2	=SUMPRODUCT((--(MONTH("1/"&$A$1:$L$1)=MONTH(TODAY())-1))*$A2:$L2)

 

[johnswaine]

Am assuming that the headers are Text and not dates formatted as Text.

The below should work, but not for jan

	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	Previous Month
2	1	1	1	1	1	1	1	1	10	1	1	1	10
3	1	1	1	1	1	1	1	1	10	1	1	1	10
4	1	1	1	1	1	1	1	1	10	1	1	1	10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet11​

Worksheet Formulas Cell Formula M2 =INDEX($A2:$L2,MATCH(TEXT(TODAY(),"mmm"),$A$1:$L$1,0)-1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Brilliant Thanks

 

[johnswaine]

Hi

also how can I return last months date in mmm format using "today" so it will automaticall show last month when I open the sheet?

 

[Momentman]

Hi

also how can I return last months date in mmm format using "today" so it will automaticall show last month when I open the sheet?

You can use

=TEXT(EOMONTH(TODAY(),-1),"mmm")