offset formula 
Author Message
 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  
 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  
 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  
 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  
 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  
 
 [ 5 post ] 

 Relevant Pages 

1. Passing arguments to my offset formula

2. Offset Formula

3. Offset formula

4. Verify my OFFSET formula is corrrect?

5. Offset formula by one row with macro

6. Newbie, Why error in OFFSET formula?

7. Offset formula?

8. blank cells in offset formula used in chart

9. embedded reference cell in offset formula

10. Offsetting ranges in a formula...


 
Powered by phpBB® Forum Software © phpBB Group