Search code examples
exceldateif-statementexcel-2010trending

Excel 2010: Adjust formula so it calculates different data based on last month

I am working on a table attached below.

I have trending data along the side. But this has to currently be manually moved each month. The formula is : =IFERROR(-SIGN(J4-I4),"N/A")

Which works as I currently want September compared to August. But I was wondering if there was a way to get this to automatically move across to the next 2 columns either when data is added into October or based on the date.

The months are currently entered as the 1st data of the month 01/10/2017 but displayed as MMM. Just in case it was necessary to use them in the formula.

I have figured out how to calculate the 1st date of the last month as well:

=IF((EOMONTH(TODAY(),-2)+1)=J2, "Yes", "No") - did it as an IF just so I could see if it had worked.

I am trying to figure out if I can add to the first formula (SIGN) so it first gets the 1st date of the last month (01/09/2017) then compares that to the month column. then depending on the month compares the corresponding data below.

enter image description here

Solution

  • Instead of =IFERROR(-SIGN(J4-I4),"N/A"), try this formula:

    =IFERROR(-SIGN(INDEX(A4:M4,1,COUNTA(A4:M4))-INDEX(A4:M4,1,(COUNTA(A4:M4)-1))),"N/A")

    Months with no data should be blank and W/O formula in it...