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)
