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

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

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

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

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

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.

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
---

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.

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

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.

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

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

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