finding real entries in a column, and shifting them to the top

[stealthbadger]

Hi,

I am trying to use the trapezium rule on the top of a cyclic loading curve like that attached here; cycliccurve.png

I have used a function like;

=IF(Q21>(MAX($Q$20:Q20)),Q21,NA())

to put #N/A into the blue cycles in the loading, so that the result column only contains the pink region of the graph, and has #N/A wherever the loops occur in the record. In order to integrate the area under the pink curve using the trapezium rule, I will need to subtract one "real" value from another in this column, so I'd like to find a way to formulate these into a column of just the "real" values.

i.e. i'd like to go from column a to column b below;


a b
na() 20
na() 35
na() 10
20 60
na()
35
na()
na()
na()
10
na()
na()
60



I suspect this would involve the match or lookup functions, but i can't work out how to do it. Any ideas?

Thanks for any help.

[Tony Valko]

Try this...

Assuming your data is in the range A2:A14.

Enter this array formula** in B2:

=IF(ROWS(B$2:B2)>COUNT(A$2:A$14),"",INDEX(A:A,SMALL(IF(ISNUMBER(A$2:A$14),ROW(A$2:A$14)),ROWS(B$2:B2))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

[stealthbadger]

Thanks! Unfortunately, this seems like it's along the right lines, but it's just copying the first value into as many rows as there are actual values, i.e;

na() 20
na() 20
na() 20
20 20
na()
35
na()
na()
na()
10
na()
na()
60



unfortunately, I don't understand what's in the equation at all, and haven't worked out how to fix it (yet!).

[Tony Valko]

Make sure calculation is set to automatic.

Tools>Options>Calculation tab>Automatic>OK