Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting columns
I have a row of data that may have some blank cells in it. Is there a way to
count the number of cells (including blanks) between say G5 and the rightmost cell with data in it using spreadsheet formulas, not vba code. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting columns
=COLUMN()-COLUMN($G$5)+1 {for inclusive column count}
=COLUMN()-COLUMN($G$5) {excludes the column with the formula} -- Steve "NDBC" wrote in message ... I have a row of data that may have some blank cells in it. Is there a way to count the number of cells (including blanks) between say G5 and the rightmost cell with data in it using spreadsheet formulas, not vba code. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting columns
I'm using v2003 and my understanding of your formula is it calculates the
difference between the cell where the formula is and cell g5. This is not quite what I want to do. I want to put the formula in cell f5 and count the difference between g5 and the last cell to the right in row 5 with data in it. What's giving me the hassles is there could be blank cells in the middle of the data range that need to be counted as well. Thanks "AltaEgo" wrote: =COLUMN()-COLUMN($G$5)+1 {for inclusive column count} =COLUMN()-COLUMN($G$5) {excludes the column with the formula} -- Steve "NDBC" wrote in message ... I have a row of data that may have some blank cells in it. Is there a way to count the number of cells (including blanks) between say G5 and the rightmost cell with data in it using spreadsheet formulas, not vba code. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting columns
Try this array formula:
=MAX((5:5<"")*COLUMN(5:5))-COLUMN($G$5) Hans "NDBC" wrote in message ... I have a row of data that may have some blank cells in it. Is there a way to count the number of cells (including blanks) between say G5 and the rightmost cell with data in it using spreadsheet formulas, not vba code. Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting columns
Hans, it doesn't seem to work. I just keep gettin 0. What I want is the last
used column to the right of column G - column G. The formula is entered in column F. There are blank cells to the left of column F. I'm not sure if this is effecting the results. Thanks for your efforts. "Hans Knudsen" wrote: Try this array formula: =MAX((5:5<"")*COLUMN(5:5))-COLUMN($G$5) Hans "NDBC" wrote in message ... I have a row of data that may have some blank cells in it. Is there a way to count the number of cells (including blanks) between say G5 and the rightmost cell with data in it using spreadsheet formulas, not vba code. Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting columns
Try this, array-entered in say F6, then copied down as required:
=MAX(IF(G5:IV5<"",COLUMN(G5:IV5)))-COLUMN(G5)+1 voila? celebrate it, hit YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "NDBC" wrote: .. What I want is the last used column to the right of column G - column G. The formula is entered in column F. There are blank cells to the left of column F. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting columns
oops, it should read as F5 in:
.. array-entered in say F6, .. -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting columns
Max,
thanks max, works a treat. One thing that worries me about array formulas is speed. I need to put this formula in 100 rows on 7 different worksheets. Do you think this will be slow. "Max" wrote: Try this, array-entered in say F6, then copied down as required: =MAX(IF(G5:IV5<"",COLUMN(G5:IV5)))-COLUMN(G5)+1 voila? celebrate it, hit YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "NDBC" wrote: .. What I want is the last used column to the right of column G - column G. The formula is entered in column F. There are blank cells to the left of column F. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting columns
try this in I Column
=COUNTIF(A1:G5,"<""") -- If this post helps, pls click Yes --------------- TGV "NDBC" wrote: I have a row of data that may have some blank cells in it. Is there a way to count the number of cells (including blanks) between say G5 and the rightmost cell with data in it using spreadsheet formulas, not vba code. Thanks |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting columns
You're welcome. Think recalc performance should not be impacted too much per
se, since its only 256 cols max. -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "NDBC" wrote: Max, thanks max, works a treat. One thing that worries me about array formulas is speed. I need to put this formula in 100 rows on 7 different worksheets. Do you think this will be slow. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting columns
NDBC
It works fine for me. Did you array-enter the formula? =MAX((5:5<"")*COLUMN(5:5))-(COLUMN($G$5)-1) returns exactly the same as Max' formula which you seemingly prefer. Hans "NDBC" wrote in message ... Hans, it doesn't seem to work. I just keep gettin 0. What I want is the last used column to the right of column G - column G. The formula is entered in column F. There are blank cells to the left of column F. I'm not sure if this is effecting the results. Thanks for your efforts. "Hans Knudsen" wrote: Try this array formula: =MAX((5:5<"")*COLUMN(5:5))-COLUMN($G$5) Hans "NDBC" wrote in message ... I have a row of data that may have some blank cells in it. Is there a way to count the number of cells (including blanks) between say G5 and the rightmost cell with data in it using spreadsheet formulas, not vba code. Thanks |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting columns
Hans,
Think the OP faced circular ref problems entering your formula in col F in the same row, ie in F5 (it'll then just return a zero, despite array-entering from my tests here) -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Hans Knudsen" wrote in message ... NDBC It works fine for me. Did you array-enter the formula? =MAX((5:5<"")*COLUMN(5:5))-(COLUMN($G$5)-1) returns exactly the same as Max' formula which you seemingly prefer. Hans |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting columns
Max
You are right. Hans "Max" wrote in message ... Hans, Think the OP faced circular ref problems entering your formula in col F in the same row, ie in F5 (it'll then just return a zero, despite array-entering from my tests here) -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Hans Knudsen" wrote in message ... NDBC It works fine for me. Did you array-enter the formula? =MAX((5:5<"")*COLUMN(5:5))-(COLUMN($G$5)-1) returns exactly the same as Max' formula which you seemingly prefer. Hans |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting in columns | Excel Worksheet Functions | |||
counting in columns | Excel Worksheet Functions | |||
Counting in Columns | Excel Worksheet Functions | |||
Counting in two columns | Excel Worksheet Functions | |||
counting columns | Excel Worksheet Functions |