0

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

Kresimir L.
  • 2,301
  • 2
  • 10
  • 22
Lozza1234
  • 27
  • 1
  • 7

1 Answers1

0

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...

Kresimir L.
  • 2,301
  • 2
  • 10
  • 22
  • 1
    Wow that works perfectly. Thank you!! Any chance you could explain it a little? I like to understand what I am doing as best I can. – Lozza1234 Oct 13 '17 at 11:09
  • You're welcome. main part of function is COUNTA which counts how many columns are populated in a row, giving you the number of the last populated column. then INDEX formula uses that column number to return you the value of last populated column in given row. To get the value of the preceding column, I used same INDEX formula, but subtracted 1 from COUNTA formula. – Kresimir L. Oct 13 '17 at 11:22