Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 12th 05, 03:58 PM posted to microsoft.public.excel.worksheet.functions
Bradley
 
Posts: n/a
Default 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   Report Post  
Old December 12th 05, 04:16 PM posted to microsoft.public.excel.worksheet.functions
Aladin Akyurek
 
Posts: n/a
Default 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   Report Post  
Old December 12th 05, 05:04 PM posted to microsoft.public.excel.worksheet.functions
Sloth
 
Posts: n/a
Default 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   Report Post  
Old December 12th 05, 05:56 PM posted to microsoft.public.excel.worksheet.functions
Bradley
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
delete cell that doesn't contain a specific word [email protected] Excel Discussion (Misc queries) 1 October 12th 05 05:54 AM
Returning the formula in a cell Sukhjeet Excel Discussion (Misc queries) 0 June 29th 05 11:24 AM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM
up to 7 functions? ALex Excel Worksheet Functions 10 April 12th 05 06:42 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 07:01 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017