good ole if function on changing values ??

Posted by mike spradau on February 19, 2001 7:30 AM

hi,

i try to solve the following problem:

i have got the following data:

a1 1
a2 2
a3 3
a4 4
a5 5

b1 1
b2 0
b3 2
b4 0
b5 7

i want column c to show:

c1 =if(b1=0, {this is where i do not know further}
'next value down in b (By) minus next value
up in b (Bx) divided by y value a(Ay) next to
By minus Bx times b1'
,b1)

clearer, this is what i want to see:

c1 =b1 ie. = 1
c2 =(b3-b1)/(a3-a1)*b2 ie. = 1.5
c3 =b3 ie. = 2
c4 =(b5-b3)/(a5-a3)*b4 ie. = 4.5
c5 =b5

important is that the zero values and all the other values can change their position.

your help is much appreciated !

rgds,
mike.

Posted by Celia on February 19, 2001 3:54 PM

Mike
The results of your formula examples do not match the data you posted.

The following is the stucture you are looking for:-
=IF(B2=0,yourformula,B2)

You would need a different formula in C1 to handle when B1 is 0.

Celia

Posted by mike spradau on February 20, 2001 6:55 AM

hi celia, thanks for looking into this. i think the if function can not work for what i want to see in the first place. i just called it "if". the point is, that there is a column with, say, dates, and only a few dates have a value next to it in the second column. what i want to create in a third column is to show values in between the two existing values (e.g. the tow existing values average weighted by date) and for the existing values it shows only them, without any calculation ...
is it possible to use for example some lookup function, ie. it is looking up the next two values not equal to zero and does a calculation (any calculation) with those two values, if it is not one of the two values ?

thanks,
mike.

Posted by Celia on February 20, 2001 3:33 PM

Mike
Sorry, I can't understand what it is you're trying to do. I think you'd better provide some actual data and the actual results you want to achieve.
Celia