0

I have odd number of columns and want to select value from the middle one, how?

Example:

1 2 3 4 5 6 7
A B C D E F G

How to select the cell "D"?

1
  • 2
    What ő you mean by select? Make the cell active? Or have a formula in another cell returning its value? Which version of Excel? Oct 13 '20 at 3:14
1

The following formula would return the value of the middle column of a range of cells. This formula assumes your data is in row 1 of the sheet (specified after "ADDRESS" in the formula). The range of cells to be assessed is shown after "COLUMNS" in the formula.

=INDIRECT(ADDRESS(1,ROUNDUP((COLUMNS(A1:G1)/2),0)))

6
  • This does't works properly if use for C1:I1 you get 2,, if for G1:M1 returns 0,, Even the array formula doesn't works !! Oct 14 '20 at 3:32
  • @RajeshS The assumption is that columns commence at column A and not partway across the spreadsheet. Oct 14 '20 at 3:37
  • 1
    Thank you! this is exactly, what I was looking for! Oct 14 '20 at 4:10
  • @JasonRiley,,,yes it was my concern,, that a efficient formula has to work every where,,,, it needs improvisation !! Oct 14 '20 at 11:40
  • @RajeshS It fits the use case and satisfies the requirements of the requestor. There's no point buying a Ferrari if all you need is a bicycle. However, you may wish to work on it further so that the formula can reference columns other than those beginning at the left of the sheet. Oct 14 '20 at 12:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.