Display value if next cell below is zero
 

I have two sets of data starting at cell ref A41, Col A displaying JAN, FEB,
MAR, APR...etc down to DEC, Col B diplaying YTD values from other workbooks:

JAN 2500
FEB 3000
MAR 4000
APR 2750
MAY 0
Etc
etc

I wish to display in CEll ref Q16 the YTD value of the month before the 1st
0 (zero) value in col B (in my example above the value displayed would be
2750...is there any way I can do this?

There will never be a 0 value shown above a value greater than 0..i.e. once
the year starts there will always be a value in JAN and the data will be
built up over the following months.

Regards

Hi
try
=INDEX(B41:B100,MATCH(0,B41:B100,0)-1))

--
Regards
Frank Kabel
Frankfurt, Germany
"MickJJ" schrieb im Newsbeitrag
...
I have two sets of data starting at cell ref A41, Col A displaying JAN,
FEB,
MAR, APR...etc down to DEC, Col B diplaying YTD values from other
workbooks:

JAN 2500
FEB 3000
MAR 4000
APR 2750
MAY 0
Etc
etc

I wish to display in CEll ref Q16 the YTD value of the month before the
1st
0 (zero) value in col B (in my example above the value displayed would be
2750...is there any way I can do this?

There will never be a 0 value shown above a value greater than 0..i.e.
once
the year starts there will always be a value in JAN and the data will be
built up over the following months.

Regards

Hi Frank

Thanks for the help, this is exactly what I needed.

Regards

Mick

"Frank Kabel" wrote:

Hi
try
=INDEX(B41:B100,MATCH(0,B41:B100,0)-1))

--
Regards
Frank Kabel
Frankfurt, Germany
"MickJJ" schrieb im Newsbeitrag
...
I have two sets of data starting at cell ref A41, Col A displaying JAN,
FEB,
MAR, APR...etc down to DEC, Col B diplaying YTD values from other
workbooks:

JAN 2500
FEB 3000
MAR 4000
APR 2750
MAY 0
Etc
etc

I wish to display in CEll ref Q16 the YTD value of the month before the
1st
0 (zero) value in col B (in my example above the value displayed would be
2750...is there any way I can do this?

There will never be a 0 value shown above a value greater than 0..i.e.
once
the year starts there will always be a value in JAN and the data will be
built up over the following months.

Regards