MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Help w/array formula


Posted by Jim on December 03, 2001 11:43 AM

This formula is in M21 and subtracts G21 from the last non-blank value above H21.

{=IF(H21="","",INDEX(H$1:H20,MAX(IF(NOT(ISBLANK(H$1:H20)),ROW(H$1:H20)))-ROW(H$1)+1)-G21)}

Now my problem: Assume H15 contains a value and H16:H20 are blank. Column A is filled with dates. How would I reference A15 (the date associated with H15 as the last non-blank cell above H21) in a formula to subtract it from the date in A21?


Posted by Aladin Akyurek on December 03, 2001 12:12 PM

Jim --

Try this non-array formula for the first part:

=IF(LEN(H21),INDIRECT(ADDRESS(MATCH(9.99999999999999E+307,H:H),8))-G21,"")

For the second part:

=INDIRECT(ADDRESS(MATCH(9.99999999999999E+307,H:H),1))-A21

Aladin

=========

Posted by Mark W. on December 03, 2001 12:14 PM

Use the following formula for the last value in H...

=INDEX(H$1:H20,MATCH(9.99999999999999E307,H$1:H20))

Use the formula for the date associated with the last value...

=OFFSET(INDEX(H$1:H20,MATCH(9.99999999999999E307,H$1:H20)),,-7)