formula that will go up one space if no value in specified space
 

I need a array formula that will find the difference between two numbers, but
I need to specify a space yet if that space does not get filled in the
formula will need to go up one space and take that number,

Example: (N39-I37) however if I37 has no number/value in it I need it to
go to I36

the sheet needs to be locked so I can not just change the formula as I go

formula that will go up one space if no value in specified space
 

On Wed, 16 Nov 2005 06:50:12 -0800, skammi
wrote:

I need a array formula that will find the difference between two numbers, but
I need to specify a space yet if that space does not get filled in the
formula will need to go up one space and take that number,

Example: (N39-I37) however if I37 has no number/value in it I need it to
go to I36

the sheet needs to be locked so I can not just change the formula as I go


You could use this *array* formula to subtract from N39 the number in the
lowest filled in cell in column I, up to row 37.


=N39-OFFSET(I1,MAX(ISNUMBER(I1:I37)*ROW(INDIRECT("1:37" )))-1,0)

Depending on your precise specifications, you will likely need to change I1 to
an absolute or mixed reference; and also change the "1:37" to something like
"1:"& fml where fml computes the maximum row number of interest.


--ron