0

I wish to find the last two payments in Column B (but not always above each other) and then obtain the date that is in column A for those payments. Then have the number of days between these payments shown in the top of Column B.

All information starts from row 10 downwards.

What formula could I use to get this information ?

Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
jasonc310771
  • 1
  • 2
  • Are the rows without payments blank, or zero? Can you add an additional column? – Ken Mar 13 '14 at 13:10
  • Do the rows without payments still have a date? – Ken Mar 13 '14 at 13:19
  • The other rows in the column are blank, yes the other rows have dates that relate to other payment columns. not sure about adding another column. I am going to try the formula below and see what happens. – jasonc310771 Mar 13 '14 at 17:26

1 Answers1

0

Assuming that your data is formatted as an Excel table, put this formula into B1 and press CTRL+SHIFT+ENTER to make it a range formula.

=MAX(IF(Table1[Pmt]>0,Table1[Date]))-LARGE(IF(Table1[Pmt]>0,Table1[Date]),2)

You would obviously have to substitute the PMT and DATE entries to match the table column headings, and replace "Table1" with your actual table name.

Thanks to @simoco for the inspiration from Given value in column A, find min/max in column B in EXCEL

EDIT To reference from a different sheet, and to not use a table:

=MAX(IF(Sheet1!$D:$D>0,Sheet1!$A:$A))-LARGE(IF(Sheet1!$D:$D>0,Sheet1!$A:$A),2)

and press CTRL+SHIFT+ENTER to make it a range formula.

Community
  • 1
  • 1
Ken
  • 1,156
  • 1
  • 6
  • 8
  • I just tried this... =MAX(IF(Sheet1[d:d]>0,Sheet1[a:a]))-LARGE(IF(Sheet1[d:d]>0,Sheet1[A:a]),2) but it will not store the formula, it says something is wrong but does not say where or what. – jasonc310771 Mar 13 '14 at 17:41
  • how do I add a file here, I was just about to post the mockup excel I created based on my own file. – jasonc310771 Mar 13 '14 at 17:54
  • Two things: (1) your missing the "!" character after the Sheet1, and (2) unless you're referencing an excel table, remove "[" and "]". This works for me on Sheet3: =MAX(IF(Sheet1!D:D>0,Sheet1!A:A))-LARGE(IF(Sheet1!D:D>0,Sheet1!A:A),2) CTRL SHIFT ENTER – Ken Mar 13 '14 at 17:56
  • Given you mentioned that your data starts on row 10, there is a danger if rows 1 - 9 of column A contain a future date, as this would be picked up by the generic A:A reference. If at all possible, look at converting the range to an excel table, which will let you use the first solution. – Ken Mar 13 '14 at 18:01
  • its still not working for me. is there a file store or something i can post my file in for this site ? – jasonc310771 Mar 13 '14 at 19:09