Thank you Asa, I to needed this information, most kind.....
Best Regards.
Mr Michael Brumby
Diese Email und samtliche beigefugten Anhange sind vertraulich zu behandeln und
sind vor Veroffentlichung geschutzt. Informieren Sie den Absender bitte
umgehend und loschen Sie diese Nachricht mit allen beigefugten Anhangen, falls
Sie nicht der vorgesehene Empfanger sind. Der Inhalt dieser Email darf nicht an
oder von dritten weitergeleitet, veroffentlicht, verwendet, oder kopiert
werden, oder durch jegliches Medium gespeichert werden.
--- On Sat, 21/4/12, Asa Rossoff <a...@lovetour.info> wrote:
From: Asa Rossoff <a...@lovetour.info>
Subject: RE: $$Excel-Macros$$ Count intial #NA in a row using excel function
To: excel-macros@googlegroups.com
Date: Saturday, 21 April, 2012, 0:30
Hello Divaker!
You provided very little information about your need.
My reading of the subject line of your message ("Count initial #NA in a row…")
leads me to believe you would like a formula that:
1. Finds the first appearance of #NA in the sequence of cells.
2. Counts the number of #NAs from that point that are in one uninterrupted
sequence.
3. Ignores all other #NAs in the range.
Here is a formula for the above:
=INDEX(FREQUENCY(IF(ISNA($A$1:$L$1),COLUMN($A$1:$L$1)),IF(NOT(ISNA($A$1:$L$1)),COLUMN($A$1:$L$1))),MATCH(TRUE,FREQUENCY(IF(ISNA($A$1:$L$1),COLUMN($A$1:$L$1)),IF(NOT(ISNA($A$1:$L$1)),COLUMN($A$1:$L$1)))>0,0))
Enter with CSE (ctrl-shift-enter) ----- array formula.
Something tells me it could be done with a shorter formula, but this is the
best I've got for you at the moment :)
See Attached sample, or the same via Google Docs:
https://docs.google.com/open?id=1nXP7jG6Obg1IHKhSr28outPlddX6zMKW3lWSd-I9_XA (@
GDocs, File>Download or File>Export to Google Spreadsheet)
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On
Behalf Of Divaker Pandey
Sent: Friday, April 20, 2012 4:36 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Count intial #NA in a row using excel function
Hi Expert,
Please see attached sheet.
Divaker
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
