0

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"

Sample

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.

Improve this question
2
  • 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
    Commented Sep 3, 2019 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
    Commented Sep 3, 2019 at 15:12

3 Answers 3

Reset to default
1

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

Improve this answer
0
0

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.

Improve this answer
0

The easiest for me after reading about INDEX, or OFFSET, was to use specific columns only to data (no SUM or anything at the bottom) and then just make use of the formulae with full range, e.g. SUMIF(A:A,B:B,">0"). Maybe this could he useful

Improve this answer
1
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Aug 11, 2023 at 11:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .