Excel 2007 : Formula using next above information

[brennan11]

I have a situation where there is a list of numbers in a column. Most of the numbers are 0's but randomly throughout the list there cells with numbers in them. I have used a filter to show only the cells with numbers.

I have a formula beside these numbers which requires information from the previous number in the list. Before the formula would just grab information from the line above, but now that I have had to place all the zeros in with the data, Im not sure how to get the formula to automatically detect it needs to use the previous number in the list which is not a 0

Thanks in advance for your help

[brennan11]

Thanks, I have uploaded a dummy workbook, hope it makes sense.

[Marcol]

Try this in C4

Please Login or Register  to view this content.

Drag/Fill Down

This will return #N/A in C4 unless there is a value in D2:D3, after then it returns what you are after, as best as I can see.

Hope this helps

[DonkeyOte]

Similar to Marcol's but uses LOOKUP rather than INDEX/MATCH and simple reference to E rather than use of volatile OFFSET

Please Login or Register  to view this content.

[Marcol]

I'll get the hang of this formula/volatility thing one day Don!

[DonkeyOte]

OFFSET is fast so it's not a real issue.

I tend to opt/demo non-volatile alternatives out of habit and based on fact I know little/nothing of the "real" model and thus can't say categorically how (in)significant the impact of a Volatile approach will be

For ex. in it's own right there's nothing to say using OFFSET in this instance is anything other than optimal.
However, if we consider the possibility that one or more of these cells could be precedent cells of an inefficient Array formula in a huge model.... well that would be entirely different...
Given I don't know either way I err on side of caution.

If you nerd long enough you come across enough models to think that suggesting Volatile solutions can often come back to haunt you...

OP: Day 1: Question: "Reference cells every 10 rows...." -> OFFSET solution
OP: Day 2: Question: "How Do I Enforce Manual Calculation" ...

Needless to say many on here think I'm a little over zealous in my attack on the Volatiles...

[Marcol]

Cheers Don.

I struggle to get my head round this, from your link.

Excel’s Volatile Functions.

Some of Excel’s functions are obviously volatile: RAND(), NOW(), TODAY()

Others are less obviously volatile: OFFSET(), CELL(), INDIRECT(), INFO()

Some are volatile in some versions of Excel but not in others: INDEX()became non-volatile in Excel 97.

A number of functions that are documented by Microsoft as volatile do not actually seem to be volatile when tested:

INDEX(), ROWS(), COLUMNS(), AREAS()

and CELL("Filename") IS volatile although a MSKBN article says its not.

Is it just me, or MS?

[DonkeyOte]

Not sure as to the question...

INDEX is documented as being Volatile but is in fact regarded as "semi-volatile"

That is to say INDEX acts as per any other Volatile in that it will calculate when the workbook is opened, however, thereafter it ceases to be Volatile (hence "semi-volatile")
You can prove this by using an INDEX call in a workbook saving it, re-opening it and closing it immediately - you will get a "Save" warning - the INDEX flags as 'dirty' - ie recalculated since last save

For range related functions like OFFSET, INDIRECT & INDEX it's often easiest to think of the precedents.
If the precedent range is implicit then the function will be Volatile.
In the case of INDEX the final range (if valid) always resides within the explicitly defined range and as such need not be volatile
(though it can have a large number of precedents pending size of initial range).

Examples:

=OFFSET(A1,4,2)
=INDIRECT("C5")

Excel can not be sure of the precedent range without first calculating the formulae... hence it recalculates with every volatile action "just in case" the precedent was altered.

=INDEX(A1:C10,5,3)

Though the single cell precedent is not defined explicitly (C5) we know it must be somewhere within the range that is specified explicitly.
So though we have 30 precedents (rather that just the 1) there is no need for Volatility so though the above may calculate more often than is ideal (ie whenever any of A1:C30 are altered) it does not calculate all the time - eg D10 being altered.