find difference between adjacent columns

[griffin]

Hi Experts,


I have a column form example "E" where i have used the formula A2-B2 to find the differencce between last and current month.Next month again i have to change the formula to c2-b2 then next month d2-c2 but column E is constant.Is there a way through excel function or macro to automate it as its always a manual task every month.

note: if im doing A2-B2 then rest of the columns will be hidden except E.Means all the columns will be hidden except last and current month column and E column.


Looking forward for your reply.


Thanks in advance.

 

[NARAYANK991]

Hi ,


You have given one example where for three different months , the formula in column E will shift from A2-B2 to C2-B2 to D2-C2 ; is this correct ?


Are these the only three possibilities ?


How are these possibilities related to the months January through December ? Or are they not related to the calendar months ?


Can you explain in more detail ?


Narayan

 

[Jai9]

Try this formula in cell E2.

=IF((MONTH(TODAY())=2),B2-A2,IF((MONTH(TODAY())=3),C2-B2,IF((MONTH(TODAY())=4),D2-C2,IF((MONTH(TODAY())=5),F2-D2,IF((MONTH(TODAY())=6),G2-F2,IF((MONTH(TODAY())=7),H2-G2,IF((MONTH(TODAY())=8),I2-H2,IF((MONTH(TODAY())=9),J2-I2,IF((MONTH(TODAY())=10),K2-J2,IF((MONTH(TODAY())=11),L2-K2,M2-L2))))))))))


I have assumed that Jan, Feb, Mar, Apr are in cells A1, B1, C1, D1. In cell E2, this formula is placed. From F1 to M2, I have put May,June etc till Dec. Cells A2 to M2 contain values for the respective months.

 

[griffin]

Hi NARAYANK991,


Thanks to respond on this.Please find the sample file where i have shown you the scenario.Please see the sheet "1 month" where column F is fixed and C,D,E are hidden columns and im finding the diff between june and may and in sheet 2 month,im finding the difference between Jul and Jun12 and similarly month by month,Aug-Jul,Sep-Aug etc.. i have to calculate like this but its a manual effort for me.Is there a formula or VBA code which can make me get rid of this.


please find the link-http://speedy.sh/GqBPU/sample.xls


Regards,

 

[NARAYANK991]

Hi ,


Good to see your sample worksheet ; however , can you clarify one point ? Is this trend supposed to go from January to December or April to March or from June to .... What you have shown as 1 month and 2 month , how many more such tabs will there be in this workbook ?


Why do you need so many tabs ? Why not have all the months of 1 full year in one tab ? It is quite easy to work out the current month - previous month even if all of the year's data is on one tab , spread over 12 columns.


Secondly , it is better to leave the future months' cells blank , rather than have #NA in those. Blank cells can be used to detect the current month , and the previous month is just one cell back from there.


Can you clarify whether it is possible to have all the data on one tab ?


Narayan

 

[griffin]

Hi Jai,


You are great thanks for the tip


Regards,

 

[griffin]

Hi Narayan,


Thanks for response,I think Jai's solution will work for me ,still i need to try tomorrow.Can you tell me what do you mean by putting all data in one tab?Anyhow this format is given from User even it can be changed if i understand what you mean.User wants to see each months data separately and want to track mont by month.


Regards,

 

[Jai9]

You are welcome Griffin !!


Jai

 

[SirJB7]

Hi, griffin!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you can get rid of the formula in E column (you now have 4 columns to the left A:D and 8 columns to the right E:M) and keep all months together (from A:L), you can place this formula in any other free column:


=INDIRECTO(DIRECCION(FILA();MES(HOY())))-INDIRECTO(DIRECCION(FILA();MES(HOY())-1)) -----> in english: =INDIRECT(ADDRESS(ROW(),MONTH(TODAY())))-INDIRECT(ADDRESS(ROW(),MONTH(TODAY())-1))


Regards!