| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Display answer only in another cell of one containing a formula | Excel Discussion (Misc queries) | |||
| How do I set a cell to "Empty" so that it does not display in a ch | Charts and Charting in Excel | |||
| Display actual contents of cell | Excel Discussion (Misc queries) | |||
| GET.CELL | Excel Worksheet Functions | |||
| how to display heading of column corresponding to the cell | Excel Worksheet Functions | |||
Linear Mode
