Fill in a column by a number column OR stretch a column

**whatever61** · 03-05-2015, 06:42 PM

I would like to achieve the following:

Put in cell A1 this formula: =AVERAGEIF(O:O,$B$2,R:R) and when I stretch it down, it will just change the R:R to the next column.
In A2 will be =AVERAGEIF(O:O,$B$2,S:S), in A3 will be =AVERAGEIF(O:O,$B$2,T:T)

I thought to achieve it somehow by putting a number of column instead of R:R and then stretching it down, then the number would increase and the column would change..

But I couldn't find the way to do it, but that's just a suggestion. Any idea would be welcome..

Thanks

**FDibbins** · 03-05-2015, 07:15 PM

There may be a better way thank this, but this works...
=AVERAGEIF(O:O,$B$2,INDIRECT(CHAR(114+ROW(D1))&":"&CHAR(114+ROW(D1))))

It is coded to start in column S, so if that changes, you will need to find the number for that letter using =CODE("S") or wherever, then plug that value into the CHAR argument

**whatever61** · 03-05-2015, 07:47 PM

Thanks!

But still a problem now, because when I stretch it doesn't change the CHAR(114) to 115, 116 and etc.. what to do?

**FDibbins** · 03-05-2015, 08:00 PM

No it doesnt, but thats what the ROW(D1) does (Char(114) is actually r)...

for row 1...
CHAR(114+ROW(D1))
=114+1 115
For row 2...
CHAR(114+ROW(D2))
=114+2=116
For row 3...
CHAR(114+ROW(D3))
=114+3=117
etc

I used D1 in my sample that I created for this, but the column (D) doesnt really matter, its teh row number that does teh work. So it could be A1, B1, AAA1, it would still work the same

**whatever61** · 03-05-2015, 08:23 PM

Ok, that works, BUT... then it comes to Z.. and then it has to switch to AA, but instead it goes to }

CHAR(122) is Z, and CHAR(123) is already }, not AA.

Any ideas here?

**Tony Valko** · 03-05-2015, 08:30 PM

Try this...

=AVERAGEIF(O:O,$B$2,INDEX(R:Z,0,ROWS(A$1:A1)))

Adjust for the correct end of range column where I've used up to column Z.

**whatever61** · 03-05-2015, 09:27 PM

That's beautiful!

Thank you both!

**Tony Valko** · 03-05-2015, 10:17 PM

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

Fill in a column by a number column OR stretch a column

LinkBack

Thread Tools

Rate This Thread

Display

Fill in a column by a number column OR stretch a column

Re: Fill in a column by a number column OR stretch a column

Re: Fill in a column by a number column OR stretch a column

Re: Fill in a column by a number column OR stretch a column

Re: Fill in a column by a number column OR stretch a column

Re: Fill in a column by a number column OR stretch a column

Re: Fill in a column by a number column OR stretch a column

Re: Fill in a column by a number column OR stretch a column

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How can I stretch out a formula across a column with only one value increasing?

[SOLVED] need formula to this logic, A column number B column blank then C column desired name

[SOLVED] Fill a column with a set string depending on number of entries in column.

Add Date in Column A Plus Number of Days in Column B and show result in Column C

Insert column and fill column upto where data is in previous column

Bookmarks

Bookmarks

Posting Permissions