Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
skammi
 
Posts: n/a
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
adding row to forumla carrera Excel Discussion (Misc queries) 9 August 23rd 05 10:24 PM
Named SUM Formula with relative refernce(s) Werner Rohrmoser Excel Worksheet Functions 2 April 20th 05 04:56 PM
What takes the place of a space in a formula? greenrocksandsodoesschoolandmylife Excel Discussion (Misc queries) 3 February 3rd 05 08:00 PM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM
Cell doesn't show formula result - it shows formula (CTRL + ' doe. o0o0o0o Excel Worksheet Functions 6 November 19th 04 04:13 PM


All times are GMT +1. The time now is 03:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"