dimanche 3 novembre 2019

Calculate ever expanding number of columns with data to the right

Currently have a spreadsheet that tracks attendance. First column is name, second column is attendance % and contains the formula I need to revise, subsequent columns simply have an X or O in them and denote whether someone attended or not (headers for these columns are dates).

Currently using a COUNTIF() I can check how many X's there are and then the formula is SUM(100/no_of_columns*COUNTIF(A3:A12))

Ideally I want to firstly replace no_of_columns with the actual number of columns with data to the right.

I've thought about replacing this with a SUM(COUNTIF('X')+COUNTIF('O')) but it seems pretty messy?

Secondly I want to replace the A12 with whatever the last column value is.

I could just make the last column a very high column value, but again feels messy and would like to know if there is a better way...

Example: https://docs.google.com/spreadsheets/d/1rjnUQP7V-U1EZTp3Z8yO7HybBCuQjf2y4LJ4Dv4ctF8/edit?usp=sharing

Aucun commentaire:

Enregistrer un commentaire