| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
finding the no. of rows in a COL filled with numbers, zeros and bl
Attempting to autorange the chart, I've tried to use Ozgrid's solutions with
named ranges, e.g.: =============== Expand Down to The Last Numeric Entry In the Refers to box type: =OFFSET($A$1,0,0,MATCH(1E+306,$A:$A,1),1) 4:Expand Down to The Last Text Entry In the Refers to box type: =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1) ================ Somehow, these formulas choke up with my mix of numbers, text strings and blanks. Unfortunately, the column can have either as the last item. COUNT, COUNTA and COUNTBLANK didn't help to resolve the issue. Is there a better way to count the number of rows with such a mix of entries? z.entropic |
|
#2
|
|||
|
|||
|
I am not sure what you are trying to do.
Are you trying to find the number of rows that have something in them. or are you trying to find the location of the last row with something in it? is this for a defined area of a larger spreadsheet, or is it for the entire spread sheet. "z.entropic" wrote: Attempting to autorange the chart, I've tried to use Ozgrid's solutions with named ranges, e.g.: =============== Expand Down to The Last Numeric Entry In the Refers to box type: =OFFSET($A$1,0,0,MATCH(1E+306,$A:$A,1),1) 4:Expand Down to The Last Text Entry In the Refers to box type: =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1) ================ Somehow, these formulas choke up with my mix of numbers, text strings and blanks. Unfortunately, the column can have either as the last item. COUNT, COUNTA and COUNTBLANK didn't help to resolve the issue. Is there a better way to count the number of rows with such a mix of entries? z.entropic |
|
#3
|
|||
|
|||
|
I'm trying to find the number of rows with mixed data and blanks between them
in the entire wprksheet. The number of rows is the same in each column. z.entropic "bj" wrote: I am not sure what you are trying to do. Are you trying to find the number of rows that have something in them. or are you trying to find the location of the last row with something in it? is this for a defined area of a larger spreadsheet, or is it for the entire spread sheet. "z.entropic" wrote: Attempting to autorange the chart, I've tried to use Ozgrid's solutions with named ranges, e.g.: =============== Expand Down to The Last Numeric Entry In the Refers to box type: =OFFSET($A$1,0,0,MATCH(1E+306,$A:$A,1),1) 4:Expand Down to The Last Text Entry In the Refers to box type: =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1) ================ Somehow, these formulas choke up with my mix of numbers, text strings and blanks. Unfortunately, the column can have either as the last item. COUNT, COUNTA and COUNTBLANK didn't help to resolve the issue. Is there a better way to count the number of rows with such a mix of entries? z.entropic |
|
#4
|
|||
|
|||
|
Try something like this...
=OFFSET($A$2,0,0,MATCH(2,1/($A$2:$A$65536<""))) Note that you cannot use a whole column reference for the MATCH function in this case. Hope this helps! In article , "z.entropic" wrote: Attempting to autorange the chart, I've tried to use Ozgrid's solutions with named ranges, e.g.: =============== Expand Down to The Last Numeric Entry In the Refers to box type: =OFFSET($A$1,0,0,MATCH(1E+306,$A:$A,1),1) 4:Expand Down to The Last Text Entry In the Refers to box type: =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1) ================ Somehow, these formulas choke up with my mix of numbers, text strings and blanks. Unfortunately, the column can have either as the last item. COUNT, COUNTA and COUNTBLANK didn't help to resolve the issue. Is there a better way to count the number of rows with such a mix of entries? z.entropic |
|
#5
|
|||
|
|||
|
z.entropic wrote:
Attempting to autorange the chart, I've tried to use Ozgrid's solutions with named ranges, e.g.: =============== Expand Down to The Last Numeric Entry In the Refers to box type: =OFFSET($A$1,0,0,MATCH(1E+306,$A:$A,1),1) 4:Expand Down to The Last Text Entry In the Refers to box type: =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1) ================ Somehow, these formulas choke up with my mix of numbers, text strings and blanks. Unfortunately, the column can have either as the last item. COUNT, COUNTA and COUNTBLANK didn't help to resolve the issue. Is there a better way to count the number of rows with such a mix of entries? z.entropic E2; =MATCH(9.99999999999999E+307,A:A) E3: =MATCH(REPT("z",255),A:A) E4: =IF(COUNT(E2:E3)=2,MAX(E2:E3),SUMIF(E2:E3,"<#N/A")) The following would define a dynamic range in A on Sheet1: =Sheet1!$A$1:INDEX(Sheet1!$A:$A,E4) |
|
#6
|
|||
|
|||
|
I like this one. Definitely more efficient than the one I offered.
I'll have to remember it. :) In article , Aladin Akyurek wrote: E2; =MATCH(9.99999999999999E+307,A:A) E3: =MATCH(REPT("z",255),A:A) E4: =IF(COUNT(E2:E3)=2,MAX(E2:E3),SUMIF(E2:E3,"<#N/A")) The following would define a dynamic range in A on Sheet1: =Sheet1!$A$1:INDEX(Sheet1!$A:$A,E4) |
|
#7
|
|||
|
|||
|
You may even like this better. Add 9's and z's if you like =MAX(MATCH({9.999E+307,"zzzz"},$A:$A)) -- Don Guillett SalesAid Software "Domenic" wrote in message ... I like this one. Definitely more efficient than the one I offered. I'll have to remember it. :) In article , Aladin Akyurek wrote: E2; =MATCH(9.99999999999999E+307,A:A) E3: =MATCH(REPT("z",255),A:A) E4: =IF(COUNT(E2:E3)=2,MAX(E2:E3),SUMIF(E2:E3,"<#N/A")) The following would define a dynamic range in A on Sheet1: =Sheet1!$A$1:INDEX(Sheet1!$A:$A,E4) |
|
#8
|
|||
|
|||
|
Interesting...but the drawback with this is that there needs to be at
least one cell containing a numerical value and another one containing a text value. Otherwise, you get a #N/A. :) In article , "Don Guillett" wrote: You may even like this better. Add 9's and z's if you like =MAX(MATCH({9.999E+307,"zzzz"},$A:$A)) -- Don Guillett SalesAid Software "Domenic" wrote in message ... I like this one. Definitely more efficient than the one I offered. I'll have to remember it. :) In article , Aladin Akyurek wrote: E2; =MATCH(9.99999999999999E+307,A:A) E3: =MATCH(REPT("z",255),A:A) E4: =IF(COUNT(E2:E3)=2,MAX(E2:E3),SUMIF(E2:E3,"<#N/A")) The following would define a dynamic range in A on Sheet1: =Sheet1!$A$1:INDEX(Sheet1!$A:$A,E4) |
|
#10
|
|||
|
|||
|
BTW. OP said there would be a mix.
-- Don Guillett SalesAid Software "Domenic" wrote in message ... Interesting...but the drawback with this is that there needs to be at least one cell containing a numerical value and another one containing a text value. Otherwise, you get a #N/A. :) In article , "Don Guillett" wrote: You may even like this better. Add 9's and z's if you like =MAX(MATCH({9.999E+307,"zzzz"},$A:$A)) -- Don Guillett SalesAid Software "Domenic" wrote in message ... I like this one. Definitely more efficient than the one I offered. I'll have to remember it. :) In article , Aladin Akyurek wrote: E2; =MATCH(9.99999999999999E+307,A:A) E3: =MATCH(REPT("z",255),A:A) E4: =IF(COUNT(E2:E3)=2,MAX(E2:E3),SUMIF(E2:E3,"<#N/A")) The following would define a dynamic range in A on Sheet1: =Sheet1!$A$1:INDEX(Sheet1!$A:$A,E4) |
|
#11
|
|||
|
|||
|
In article ,
"Don Guillett" wrote: BTW. OP said there would be a mix. Yes, you're right. But I think in this case it's probably best to provide a solution, like your last formula, that deals with the potential for different possibilities. |
|
#12
|
|||
|
|||
|
Don Guillett wrote:
Longer but addresses all but truly blank =IF(ISNA(MATCH(99999,N:N)),MATCH("zzzzzz",N:N),MAT CH(99999,N:N)) Apart from invoking risky 99999, what you're suggesting is not robust, therefore incorrect. Try the suggestion on: N1: 1 N2: (empty) N3: ="" That is, A3 houses a formula blank. The result you should get is 3. |
|
#13
|
|||
|
|||
|
Don Guillett wrote:
BTW. OP said there would be a mix. Great defense. How about the case when a mix doesn't materialize? This can easily happen if the range of interest is ridden with formulas that either succeeds producing only numbers or only text values. |
|
#14
|
|||
|
|||
|
Thank you-works great!
I'm glad my question has started such a spirited exchange, but in the end it confused the heck out of me! What's the final word? z.entropic "Aladin Akyurek" wrote: z.entropic wrote: Attempting to autorange the chart, I've tried to use Ozgrid's solutions with named ranges, e.g.: =============== Expand Down to The Last Numeric Entry In the Refers to box type: =OFFSET($A$1,0,0,MATCH(1E+306,$A:$A,1),1) 4:Expand Down to The Last Text Entry In the Refers to box type: =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1) ================ Somehow, these formulas choke up with my mix of numbers, text strings and blanks. Unfortunately, the column can have either as the last item. COUNT, COUNTA and COUNTBLANK didn't help to resolve the issue. Is there a better way to count the number of rows with such a mix of entries? z.entropic E2; =MATCH(9.99999999999999E+307,A:A) E3: =MATCH(REPT("z",255),A:A) E4: =IF(COUNT(E2:E3)=2,MAX(E2:E3),SUMIF(E2:E3,"<#N/A")) The following would define a dynamic range in A on Sheet1: =Sheet1!$A$1:INDEX(Sheet1!$A:$A,E4) |
|
#15
|
|||
|
|||
|
Hi!
What's the final word? Good question! The method least likely to fail and the one that's most efficient would be Aladin's. (it usually is!) This thread is a good representation that demonstrates how many different ways there are to accomplish a task. Options are a good thing! Biff "z.entropic" wrote in message ... Thank you-works great! I'm glad my question has started such a spirited exchange, but in the end it confused the heck out of me! What's the final word? z.entropic "Aladin Akyurek" wrote: z.entropic wrote: Attempting to autorange the chart, I've tried to use Ozgrid's solutions with named ranges, e.g.: =============== Expand Down to The Last Numeric Entry In the Refers to box type: =OFFSET($A$1,0,0,MATCH(1E+306,$A:$A,1),1) 4:Expand Down to The Last Text Entry In the Refers to box type: =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1) ================ Somehow, these formulas choke up with my mix of numbers, text strings and blanks. Unfortunately, the column can have either as the last item. COUNT, COUNTA and COUNTBLANK didn't help to resolve the issue. Is there a better way to count the number of rows with such a mix of entries? z.entropic E2; =MATCH(9.99999999999999E+307,A:A) E3: =MATCH(REPT("z",255),A:A) E4: =IF(COUNT(E2:E3)=2,MAX(E2:E3),SUMIF(E2:E3,"<#N/A")) The following would define a dynamic range in A on Sheet1: =Sheet1!$A$1:INDEX(Sheet1!$A:$A,E4) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
| Finding minimum value across selected rows of an array | Excel Worksheet Functions | |||
| Finding min,max in an array using selected rows from a table | Excel Worksheet Functions | |||
| Finding common data in multiple columns and rows in Excel | Excel Worksheet Functions | |||
| Adding Rows to Master Sheet | New Users to Excel | |||
Linear Mode
