查找特定列中值不等于0的最后一个单元格的行号(Finding row number of last cell with value not equal to 0 in specific column)

查找特定列中值不等于0的最后一个单元格的行号(Finding row number of last cell with value not equal to 0 in specific column)

我有一个Google表格来计算我的每月费用。 其中一列包含日常开支的总和。 我想通过将所有费用的总和（截至当天）除以天数来计算每天的平均费用。 所以我的想法是获取值大于0.0的最后一个单元格的行号。 例如，假设我有一个像这样的列：

8/16/2015 0.0 8/17/2015 0.0 8/18/2015 13.7 8/19/2015 0.0 8/20/2015 0.0 8/21/2015 0.0 8/22/2015 0.0 8/23/2015 82.2 8/24/2015 0.0 8/25/2015 0.0 8/26/2015 0.0 8/27/2015 0.0 8/28/2015 0.0

所以值大于0的最后一个单元格是连续的，日期是8/23/2015 。 我想得到它的行号或从某个日期提取23 。

I have a Google Sheet calculating my monthly expenses. One of the columns contains sums of daily expenses. I want to calculate average expenses per day by dividing sum of all costs (up to current day) by number of days. So my idea is to get the row number of the last cell with value greater than 0.0. For example, let's say I have a column like:

8/16/2015 0.0 8/17/2015 0.0 8/18/2015 13.7 8/19/2015 0.0 8/20/2015 0.0 8/21/2015 0.0 8/22/2015 0.0 8/23/2015 82.2 8/24/2015 0.0 8/25/2015 0.0 8/26/2015 0.0 8/27/2015 0.0 8/28/2015 0.0

So the last cell with value greater than 0 is one in a row with date 8/23/2015. I want to get its row number or somehow extract 23 from the date.

最满意答案

如果8/16/2015在A1，请尝试：

=ArrayFormula(max(if(B:B>0,A:A)))

如果公式在C1中，而您只需要23 ，则尝试=day(C1)

If 8/16/2015 is in A1 please try:

=ArrayFormula(max(if(B:B>0,A:A)))

If the formula is in C1 and you want just 23, try =day(C1)