Google Sheets – How to Return Value in Bottom Row of a Column

formulasgoogle sheets

I want to a cell to just print what the last cell in a column. If I were to have the column starting on row 17,

1
3
5

I want it to print five, but if I updated the list to

I want it to print 7, but I know nothing about the functions of spreadsheets.

Best Answer

Formula

=INDEX(A17:A,COUNT(A17:A))

Explanation

Assuming that the data is on the column A, that it only has numbers, and that there are not blank cells between two cells with numbers, COUNT(A17:A) will return the count of cells having a number and it will be the index of the last cell with a number on column A. Index will return the value of the last cell that has a number in column A.

NOTES

If the values are TEXT instead of NUMBER use COUNTA instead of COUNT

If the values are mixed, TEXT and other type like NUMBER / DATE / TIME / DURATION , instead of the above formula use

=ARRAYFORMULA(INDEX(A17:A, MAX(IF(ISBLANK(A17:A),,ROW(A17:A)))))

=ARRAYFORMULA(INDEX(A17:A, MAX(IF(LEN(A17:A),ROW(A17:A),)))))

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
 if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
   var r = s.getActiveCell();
   if( r.getColumn() == 13 ) { //checks the column
     var nextCell = r.offset(0, 1);
     if( nextCell.getValue() === '' ) //is empty?
       var time = new Date();
       time = Utilities.formatDate(time, "GMT", "HH:mm:ss");
       nextCell.setValue(time);
   };
 };
}

Google-sheets – Applying a Formula to All Cells in a Column (Even New Cells)

Try:

=ArrayFormula(if(len(H2:H)*len(G2:G), iferror(H2:H/G2:G, 0),))

The formula will output results if there are entries in col H AND col G. See if that works for you ?

Google Sheets – How to Return Value in Bottom Row of a Column

Best Answer

Formula

Explanation

NOTES

See also

Related Topic

Best Answer

Formula

Explanation

NOTES

See also

Related Solutions

Google Sheets Timestamp – Automatically Set ‘Last Updated’ Cell

Google-sheets – Applying a Formula to All Cells in a Column (Even New Cells)

Related Topic