Excel, offset formula

Ian Thiselto
#1 / 5

offset formula

I need to formula to get a value from an offset cell where the value of
the cell is less than or equal to 0.

for example

A B C
1 100 12 july 16 july
2 50 13 july 16 july
3 20 14 july 16 july
4 10 15 july 16 july
5 0 16 july 16 july
6 -10 17 july
7 -20 18 july

If the values in columns A or B change then the value of C also need to
change.

Thanks

ian

Tue, 06 Jan 2004 20:03:42 GMT

Paul Ruden
#2 / 5

offset formula

Quote:

> I need to formula to get a value from an offset cell where the value of
> the cell is less than or equal to 0.

> for example

> A B C
> 1 100 12 july 16 july
> 2 50 13 july 16 july
> 3 20 14 july 16 july
> 4 10 15 july 16 july
> 5 0 16 july 16 july
> 6 -10 17 july
> 7 -20 18 july

> If the values in columns A or B change then the value of C also need to
> change.

> Thanks

> ian

Your question is not very clear to me but I guess you want to
calculate the values in column C. Well the following function does
that:
=IF(A1<0,"",INDEX($B$1:$B$7,MATCH(0,$A$1:$A$7,0)))
=IF(A2<0,"",INDEX($B$1:$B$7,MATCH(0,$A$1:$A$7,0)))
=IF(A...<0,"",INDEX($B$1:$B$7,MATCH(0,$A$1:$A$7,0)))

Pretty slow if you have a big sheet.
better is in cell D1:
=INDEX($B$1:$B$7,MATCH(0,$A$1:$A$7,0))
and then:
=IF(A1<0,"",$d$1)
etc...

Regards,
Paul

Wed, 07 Jan 2004 04:00:25 GMT

Debra Dalgleis
#3 / 5

offset formula

What value do you need the cell to get?
What is the offset cell?
Which cell is less than or equal to 0?

Summer weekends are slow around here, so if you can provide a bit more
info, probably someone would be happy to help with a solution.

Quote:

> I need to formula to get a value from an offset cell where the value of
> the cell is less than or equal to 0.

> for example

> A B C
> 1 100 12 july 16 july
> 2 50 13 july 16 july
> 3 20 14 july 16 july
> 4 10 15 july 16 july
> 5 0 16 july 16 july
> 6 -10 17 july
> 7 -20 18 july

> If the values in columns A or B change then the value of C also need to
> change.

> Thanks

> ian

Wed, 07 Jan 2004 05:37:56 GMT

Ian Thiselto
#4 / 5

offset formula

Thanks Deb

I need to populate various cells in column C with the value from column
B where column A is less than or equal to 0. Columns A and B are
variable in that they can change based on the outcome of other
calculations.

I would like to do this using a formula if possible.

Hope this makes it a bit more clearer.

cul8r
Ian

Quote:

> What value do you need the cell to get?
> What is the offset cell?
> Which cell is less than or equal to 0?

> Summer weekends are slow around here, so if you can provide a bit more
> info, probably someone would be happy to help with a solution.

> > I need to formula to get a value from an offset cell where the value of
> > the cell is less than or equal to 0.

> > for example

> > A B C
> > 1 100 12 july 16 july
> > 2 50 13 july 16 july
> > 3 20 14 july 16 july
> > 4 10 15 july 16 july
> > 5 0 16 july 16 july
> > 6 -10 17 july
> > 7 -20 18 july

> > If the values in columns A or B change then the value of C also need to
> > change.

> > Thanks

> > ian

Wed, 07 Jan 2004 06:19:39 GMT

Debra Dalgleis
#5 / 5

offset formula

You can use a formula like this (in cell C6 in this example):
=IF(A6<=0,B6,"")

I'm not sure what value you want in column C if A is greater than 0.
This formula will insert the value from column B if A is less than or
equal to zero.
Otherwise, the cell will appear blank ("")

Quote:

> Thanks Deb

> I need to populate various cells in column C with the value from column
> B where column A is less than or equal to 0. Columns A and B are
> variable in that they can change based on the outcome of other
> calculations.

> I would like to do this using a formula if possible.

> Hope this makes it a bit more clearer.

> cul8r
> Ian

> > What value do you need the cell to get?
> > What is the offset cell?
> > Which cell is less than or equal to 0?

> > Summer weekends are slow around here, so if you can provide a bit more
> > info, probably someone would be happy to help with a solution.

> > > I need to formula to get a value from an offset cell where the value of
> > > the cell is less than or equal to 0.

> > > for example

> > > A B C
> > > 1 100 12 july 16 july
> > > 2 50 13 july 16 july
> > > 3 20 14 july 16 july
> > > 4 10 15 july 16 july
> > > 5 0 16 july 16 july
> > > 6 -10 17 july
> > > 7 -20 18 july

> > > If the values in columns A or B change then the value of C also need to
> > > change.

> > > Thanks

> > > ian

Wed, 07 Jan 2004 06:58:31 GMT