Difference between the first entry and last entry

I want to find the difference between the first entry and the last entry for the numbers in the "Current" column.

I am using this formula:

=INDEX([Current]:[Current]), MATCH(MAX([Date]:[Date]), [Date]:[Date], 0)) - INDEX([Current]:[Current]), MATCH(MIN([Date]:[Date]), [Date]:[Date], 0))

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

Try removing the closing parenthesis after each instance of Current:Current.

Amy Arnold

@Paul Newcome Removing the closing parenthesis gave me a zero value in each row.

Updated formula:
=INDEX(Current:Current, MATCH(MAX(Date:Date), Date:Date, 0)) - INDEX(Current:Current, MATCH(MIN(Date:Date), Date:Date, 0))

Paul Newcome

Right. That's because (based on your screenshot) your mind and max dates are the same. The MATCH function stops on the first match which means they are both pulling from the first row.

You're going to need to insert an auto-number column which can then be leveraged in a few different ways depending on exactly what your logic is.

Amy Arnold

@Paul Newcome My bad, I should have shown several dates in my screenshot.

Here I filtered the sheet by one of the sites to show several dates. The formula returns zeros throughout the "Cumulative…" column.

I tried a similar formula with the Auto column and I received a #NO MATCH.
How can I leverage the auto-number column?

Paul Newcome

Are you trying to get it to calculate by center? I see you still have a filter in place. The formula will pull from all rows wvwn if they are not shown by the filter, so it is hard to tell what the actual data is. How exactly is the [Current] column being populated?

Amy Arnold

@Paul Newcome I am trying to calculate by [Current].

I filtered the sheet to show I have different dates in the sheet and that the formula returned zeros throughout the "Cumulative…" column.

I set up automation to move rows from another sheet that collects entries via a form to this sheet daily. The data in this sheet is used to display information on a dashboard and archive daily entries.

It might be easier to share the sheet with you?

https://app.smartsheet.com/sheets/J86H5pXcFg8GWqrF3hcjgpCFCcFxw2fvpVc326P1

Paul Newcome

If you break each of the INDEX/MATCH formulas out separately, do you get any returned values for either or both of them?

Amy Arnold

@Paul Newcome I think so. Here's the current version of the formula:

=INDEX(Current:Current, MATCH(MAX(Date:Date), Date:Date, 0)) - INDEX(Current:Current, MATCH(MIN(Date:Date), Date:Date, 0))

If I set up the formula like this, I get #INCORRECT ARGUMENT.

=INDEX(MATCH(Current:Current, MAX(Date:Date), Date:Date, 0)) - INDEX(MATCH(Current:Current, MIN(Date:Date), Date:Date, 0))

Paul Newcome

What do you get when you do each of the INDEX/MATCH formulas separates. As in…. Put them each into two different cells and see what numbers each returns?

Sign in to join the conversation!

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.