Google Sheets – How to Clip Text End When Rotated Up

google sheetsgoogle-sheets-cell-format

I have a very large table where the columns and rows are named, and interior cells are "1" or empty (essentially a sparse matrix). I have rotated the column header text direction up so that the columns may be narrow. Here is a simplified example:

Some column names are very long, so the cells cannot be the entire height of the column names. When I resize the column name row to be smaller height, the text is clipped in the wrong direction. Prefixes of the names are incorrectly cut off instead of suffixes as desired. See cell B1 in simplified example of the problem:

How can I change the direction that text clipping occurs?

Best Answer

You have a long header cell that you have rotated to save space. However, when the height of the row is shortened, a "long" value is effectively right-aligned so that the end of the value is shown rather than the beginning.

As noted by @marikamitsos, it's not possible to do what you wish.

However, wrapping the text is a good work-around to display the entire cell while keeping a relatively narrow column width.

Related Solutions

How to Calculate Weighted Averages in Google Sheets

There is no built in function to calculate the weighted average so you have to write a custom function if you want to avoid using too many temporary cells. So here is how you can achieve your goal.

Go to Tools > Scripts > Script editor..., Copy/Paste the bellow code and save it:

function weightedAverage(v, w) {
  var sum_v = 0;
  var sum_w = 0;

  if (v[0].length != w[0].length) {
    return "#ERROR: Incorrect number of values and weights";  
  }

  for (var c = 0; c < v[0].length; ++c) {
    sum_v += v[0][c] * w[0][c]; 
    sum_w += w[0][c]; 
  }

  return sum_v/sum_w;
}

Use it like:

=weightedAverage(B3:D3,$B$2:$D$2)

Where B3:D3 are your values and $B$2:$D$2 your weights. It's not error proof (the only check is to make sure both arrays have the same length) but it will do the trick.

In the above example I'm not trying to extract the weights from the title of the attribute, but I'm reading them from the second row (B2:D2) to make our life easier and clearer. The $ is not changing the result of the formula. It's only affecting what's happening when you're copying the formula in another cell. The part of the cell reference following the $ will not change (link for more details). Write the formula once in the cell E3 and Copy it to the rest of the rows to see it in action.

Google-sheets – Adding a few lines of information to a grid

I suggest using the Horizontal axis label area;

WA40726 example

Best Answer

Related Solutions

How to Calculate Weighted Averages in Google Sheets

Google-sheets – Adding a few lines of information to a grid

Related Topic