基于 gru 公式_隐藏基于日期的公式结果

基于 gru 公式

My challenge this week was to show the running total in a list, but only for the current date and earlier. I wanted rows for future dates to appear empty, but have the formulas in them – ready to go. How would you solve that problem?

我本周的挑战是在列表中显示运行总计，但仅显示当前日期或更早的时间。 我希望将来日期的行显示为空，但要准备好其中的公式。 您将如何解决该问题？

跟踪活动 (Keeping Track of Activities)

The project that I was working on was a simple list, to keep track of my daily walks.

我正在从事的项目很简单，可以跟踪我的日常活动。

My daughter works for the Movember Foundation, and she forced, errr, encouraged me to sign up. This year, you can MOVE every day, to support the cause, instead of growing a Moustache. So, I have an activity page on the Movember site, but I made my own tracking sheet, in Excel of course!

我女儿为Movember基金会工作，她强迫我鼓励我报名参加。 今年，您可以每天移动以支持事业，而不必长胡子。 因此，我在Movember网站上有一个活动页面 ，但是我当然用Excel制作了自己的跟踪表！

I filled in all the November dates in the first column, and each day I'll enter the number of minutes that I walked.

我在第一列中填写了所有11月的日期，每天我将输入步行的分钟数。

添加运行总计 (Add Running Totals)

Then, to see my progress, I added formulas for running totals, in columns D and E.

然后，为了查看进度，我在D和E栏中添加了运行总计的公式。

• • =SUM($C$4:$C4)
    = SUM($ C $ 4：$ C4)

  In column D, a SUM formula gives a running total, by summing the numbers from C4, down to the current row:

  在D列中，一个SUM公式通过将C4中的数字累加到当前行， 得出运行总计 ：

• • =COUNT($C$4:$C4)
    = COUNT($ C $ 4：$ C4)

  In column E, a COUNT formula gives a running count, by counting the numbers from C4, down to the current row:

  在E列中， COUNT公式通过计算从C4到当前行的数字来给出运行计数：

That worked well, but I didn't like all the numbers showing up in the upcoming date rows. How could they be hidden?

效果很好，但是我不希望在即将到来的日期行中显示所有数字。 如何隐藏它们？

As always with Excel, there are may ways to do what you need, but here's how I solved the problem.

与使用Excel一样，有很多方法可以满足您的需求，但这就是我解决问题的方法。

没有条目，没有结果 (No Entry, No Results)

Sometimes, I add an IF function in a formula, to hide the result until a specific column is filled in.

有时，我在公式中添加IF函数，以隐藏结果，直到填充特定列为止。

In this sheet, the dates are all filled in, so I couldn't use that column as a test. Instead, I'll check for an entry in column B (Activity).

在此工作表中，所有日期均已填写，因此我无法将该列用作测试。 相反，我将检查B列(活动)中的条目。

=IF([@Activity]="","",COUNT($C$4:$C4))

= IF([@ Activity] =“”，“”，COUNT($ C $ 4：$ C4))

That worked, but it didn't just hide the results for upcoming days – it also hid the result if I skipped a day for some reason. (This is just a test – I did walk on Tuesday!)

这行得通，但它不仅隐藏了以后几天的结果，而且由于某种原因我跳过了一天，它也隐藏了结果。 (这只是一个测试-我星期二确实走了！)

Maybe I'm too picky, but I'd prefer to see the running total in that row too.

也许我太挑剔了，但是我也希望看到该行的总运行量。

检查日期 (Check the Date)

So, what I really want to do is show the running total for every day, up to the current date.

因此，我真正想做的是显示直到当前日期为止每天的运行总计。

To calculate the current date, we can use the TODAY function. To check if the entry in column A (Date) is after today:

要计算当前日期，我们可以使用TODAY函数。 要检查A列(日期)中的条目是否在今天之后：

=[@Date]>TODAY()

= [@日期]> TODAY()

I'll add that test to the running total formulas:

我将该测试添加到正在运行的总公式中：

  =IF([@Date]>TODAY(),"",SUM($C$4:$C4))

= IF([@ Date]> TODAY()，“”，SUM($ C $ 4：$ C4))

  =IF([@Date]>TODAY(),"",COUNT($C$4:$C4))

= IF([@ Date]> TODAY()，“”，COUNT($ C $ 4：$ C4))

Now, if the date in column A is after today's date, the result will be an empty string, so the cell will appear empty.

现在，如果A列中的日期晚于今天的日期，则结果将为空字符串，因此单元格将显示为空。

• Running totals will appear in all rows for today or earlier, even if there was no activity.
  即使没有活动，运行总计也将显示在今天或之前的所有行中。
• No running totals in rows with future dates (Nov. 6th and later)
  未来日期(11月6日及以后)的行中没有运行总计

下载样本文件 (Download the Sample File)

You could adapt this technique for other types of lists – it's not just for Movember MOVE tracking!

您可以将该技术用于其他类型的列表-不仅是Movember MOVE跟踪！

You can download a copy from the Excel Sample Files page on my website -- in the Functions section, look for FN0042 - MOVEMBER Activity Tracker Chart.

您可以从我的网站上的“ Excel样本文件”页面下载副本-在“功能”部分中，查找FN0042-MOVEMBER活动跟踪器图表 。

And if you want to support the cause, click this link to go to my Movember page.

如果您想支持该原因， 请单击此链接转到我的Movember页面 。

翻译自: https://contexturesblog.com/archives/2015/11/05/hide-formula-results-based-on-date/

基于 gru 公式