formula in Google Sheets where cell equals the last cell in a row that isn't 0

0
votes

Hello I am looking for a formula that makes a cell equal to the last number in a row that is not a 0.

I have some metrics that I am using Google sheets to gauge and graph. Our directors are inputting the last month they have metric data. I currently have a "Meter" column and a column for each month. I want the Meter field to equal the last month the director has recorded information, rather than them filling it in two places, the month and the meter. I'm finding that some directors are filling out the month and not changing the meter field so I would like to make the meter field automatically equal to the last month they inputted data.

I have shared a sheet with this portion of the sheet I'm using for you to look at how I have it laid out.

https://docs.google.com/spreadsheet/ccc?key=0Ai_2YLvaQba0dHdCUVRiSTE4NWwwMGRrMHBQeE9QVkE

2
google-sheetsgoogle-sheets-formula

2 Answers

1
votes

My solution:

=ArrayFormula(IF(LEN(A4:A);MMULT(C4:U*(COLUMN(C4:U4)=QUERY(VALUE(REGEXREPLACE(TRANSPOSE(SPLIT(CONCATENATE(REPT(ROW(A4:A)&CHAR(9)&COLUMN(C4:U4)&CHAR(10);(LEN(A4:A)>0)*(C4:U<>0)));CHAR(10)));"(.*)\t(.*)";{"$1","$2"}));"select max(Col2) group by Col1 label max(Col2) ''"));TRANSPOSE(SIGN(COLUMN(C4:U4))));IFERROR(1/0)))
1
votes

This is a bit simpler and will always grab the last value for you:

=REGEXEXTRACT(SUBSTITUTE(join(";",C4:4),";0",),"^.*;(\d+|-\d+);+$")