Returning specific cell if zero
I am trying to write a formula to return a cell if zero is listed.
My example is CURRENT MONTH 4776 JAN 4308 FEB 4388 MAR 4688 APR 4776 MAY 0 JUNE 0 JULY 0 AUG 0 SEPT 0 OCT 0 NOV 0 DEC 0 The CURRENT MONTH will always list the cell above the first zero listed, since MAY is the first zero listed then April would be listed in the CURRENT MONTH. I have tried IF statements. I am not very good yet with ARRAYS and INDEXS. Any help would be great!!! Thanks Bradley |
Returning specific cell if zero
Assuming A5:B16 as the range that houses the sample you posted...
=INDEX(B5:B16,MATCH(TRUE,B5:B16=0,0)-1) which needs to be confirmed with control+shift+enter. Bradley wrote: I am trying to write a formula to return a cell if zero is listed. My example is CURRENT MONTH 4776 JAN 4308 FEB 4388 MAR 4688 APR 4776 MAY 0 JUNE 0 JULY 0 AUG 0 SEPT 0 OCT 0 NOV 0 DEC 0 The CURRENT MONTH will always list the cell above the first zero listed, since MAY is the first zero listed then April would be listed in the CURRENT MONTH. I have tried IF statements. I am not very good yet with ARRAYS and INDEXS. Any help would be great!!! Thanks Bradley |
Returning specific cell if zero
=INDIRECT("B"&MATCH(0,B3:B14,0)+1)
"Bradley" wrote: I am trying to write a formula to return a cell if zero is listed. My example is CURRENT MONTH 4776 JAN 4308 FEB 4388 MAR 4688 APR 4776 MAY 0 JUNE 0 JULY 0 AUG 0 SEPT 0 OCT 0 NOV 0 DEC 0 The CURRENT MONTH will always list the cell above the first zero listed, since MAY is the first zero listed then April would be listed in the CURRENT MONTH. I have tried IF statements. I am not very good yet with ARRAYS and INDEXS. Any help would be great!!! Thanks Bradley |
Returning specific cell if zero
Thank you....worked great!!!!!!
"Aladin Akyurek" wrote: Assuming A5:B16 as the range that houses the sample you posted... =INDEX(B5:B16,MATCH(TRUE,B5:B16=0,0)-1) which needs to be confirmed with control+shift+enter. Bradley wrote: I am trying to write a formula to return a cell if zero is listed. My example is CURRENT MONTH 4776 JAN 4308 FEB 4388 MAR 4688 APR 4776 MAY 0 JUNE 0 JULY 0 AUG 0 SEPT 0 OCT 0 NOV 0 DEC 0 The CURRENT MONTH will always list the cell above the first zero listed, since MAY is the first zero listed then April would be listed in the CURRENT MONTH. I have tried IF statements. I am not very good yet with ARRAYS and INDEXS. Any help would be great!!! Thanks Bradley |
All times are GMT +1. The time now is 03:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com