Right now I am manually adding ranges to the formula I would like to see if it is possible to add into the formula to automatically extend to the last cell without manual input

Column range =COUNTIF(D5:D273, "TRUE")/COUNTA(D5:D273)

Row range =COUNTIF(D5:AM5, "TRUE")/COUNTA(D5:AM5)

Current formula counts all cells in range for "True" Statement compared to all cells within range with any data and makes a percentile of that data to see what the average of passing score is.

I have googled and found OFFSET statements as well as INDEX statement and neither seem to work for what I need these formulas to perform

Having the formula automatically map out to the end of the range will be extremely time saving as I would only need to copy and past and then drag auto fill down the ranges without manually grabbing the end of the row or column.

The values are all text with a "TRUE" or "FALSE"


I have attached a very small sample size, of what it looks like

row 3 started from column E is where formula starts for columns

column D starting in row 3 is where formula starts for rows

The shown picture is how the data is delivered from our script, but instead of 6 machines its 15,000 and instead of 3 tests it is 400.

  • 2
    And you are sure they are not booleans? TRUE is not the same as "TRUE" If TRUE is in D5 and you put this formula =TYPE(D5) into an unused cell, do you get 2 or 4 as a result? – user385793 Sep 3 '19 at 15:02
  • Those are almost positively real booleans, not text. Unless you changed the cell alignment, only booleans and errors default to center alignment. – user385793 Sep 3 '19 at 15:12

If you have a blank cell on the right and bottom border, you can create a range that goes up to the blanks by using MATCH and OFFSET (or INDEX).

Here's an example. The MATCH function searches everything to the right for the first blank and returns and index of 5. OFFSET returns the last non-blank cell. Then the B7:OFFSET... complete the range.

The columns averages are very similar except offset by rows instead of columns in the OFFSET function. E.g. for B2:

=COUNTIF(B3:OFFSET(B3,MATCH(TRUE,(B3:B1048576)=(""),0)-2,0), TRUE)/COUNTA(B3:OFFSET(B3,MATCH(TRUE,(B3:B1048576)=(""),0)-2,0))

enter image description here


I think all you need to do is make the ranges really big for the formulas that are calculating the percentages.

For example, the formula for the average score would be

=COUNTIF(E3:XFD1048576, TRUE)/COUNTA(E3:XFD1048576)

So any time you add a row or column to your spreadsheet, it'll be picked up by this formula.

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.