counting columns

  • Thread starter Thread starter NDBC
  • Start date Start date
N

NDBC

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
 
=COLUMN()-COLUMN($G$5)+1 {for inclusive column count}
=COLUMN()-COLUMN($G$5) {excludes the column with the formula}
 
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
 
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.
 
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.
 
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
 
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
 
Back
Top