specific cell to OFFSET from

Posted by Dave on February 08, 2002 5:30 AM

I have a long column (col. Z) of data which returns zeros and a single "1.0" depending on the minimum value in 2 adjacent columns. I would like to have a summary of data at the top of the worksheet and to do this I need to have a specific cell from in column B that corresponds to the "1" in column Z.

Although it would be an extremely long way of doing things, I could use "IF(Z10=1,OFFSET(Z10,0,-24), (IF(Z11=1,OFFSET(Z11,0,-24),
(IF(Z12=1,OFFSET(Z12,0,-24),..." although the IF command doesn't allow a long enough formula to be entered.

Is there a way of telling the summary cell to find the "1.0" in col.Z and then display the corresponding col.B value?

Thanks.

Posted by Mike H on February 08, 2002 7:35 AM

I think that you could use a sumif function. Suppose the range Z1:Z1000 contains the range with zeros and a single 1. Column B is the column that is offset by -24 columns. Then you will refer to the corresponding cell in Column B by using the function =SUMIF(Z$1:$Z$1000,1,$B$1:$B$1000)

Mike H

Posted by Dave on February 08, 2002 7:42 AM

Cheers Mike - that worked perfectly!

Dave