| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| delete cell that doesn't contain a specific word | Excel Discussion (Misc queries) | |||
| Returning the formula in a cell | Excel Discussion (Misc queries) | |||
| Possible Lookup Table | Excel Worksheet Functions | |||
| up to 7 functions? | Excel Worksheet Functions | |||
| GET.CELL | Excel Worksheet Functions | |||
Linear Mode
