Formulas? Pah!

Welcome back to Twice Daily Dose of Excel. Heck, these days we’re more regular than Julian Assange’s visits to the Ecuadorian Embassy!

Audrey has a table that looks something like this (if you’re American):

Table US

…or like this (if you live somewhere sensible, like in New Zealand and a good deal many other places besides):

Table English

She uses this to track when she requested a piece of information from someone, and the date she received a response. I’m picking she’s NSA, and is asking Julian – who is actually a quadruple agent – whether he needs more sun lamps. Stranger things have happened, recently.

Anyways, she’d like a formula to find the oldest/latest request date out of all responses received in a particular month.

She could array enter something like this, which will do the job without any need for helper columns:

=MAX($A$2:$A$20*(DATE(YEAR($B$2:$B$20),MONTH($B$2:$B$20),1)=DATE(YEAR($B2),MONTH($B2),1)))

…which to an Excel Pro with a lifetime of formulas under their belt would look like this:

=NOT(RocketScience)

…but to anyone else:

="Αυτό θα μπορούσε κάλλιστα να είναι γραμμένο στην ελληνική γλώσσα"

You could debate whether there’s a right formula to use in a situation like this. In fact there’s some great debate on that original blogpost as to whether one proposed solution is awesome, potentially obfuscating, or incomprehensibly mutant. So with this in mind, is there a right formula to use in this case? Depends on who’s trying to comprehend what’s happening here in 6 months time. Perhaps yourself, with six more months of grey-matter dieback under your belt. Or hat, rather. Is there a right non-formula approach to Audrey’s problem? You betcha:

Just create a PivotTable out of that sucker:

Blank PivotTable

…drag the ‘Date Requested’ field to the Rows pane and the ‘Date Received’ to the Values pane:

PivotTable

…launch the Value Field Settings dialog for the Date Requested field:

Launch Value Field Settings

…change the name of the Date Received field to something meaningful, and change Sum to Max:

Value Field Settings Dialog

…plus click on that Number Format button while you’re there so you can change the format to Date:

Number Format

…then select any cell in the Date Received column and click Group Selection from the PivotTable Tools > Analyze contextual tab, and group by Months and Years:

Group Selection

…and exhale:

Pivot

No, waithold that breath…we forgot to change the name of that ‘Years’ column that just appeared – as well as the ‘Date Received’ column that now holds Months only – to something more suitable:

Pivot finished

Now exhale.

Here’s the genius…it even works in American:

Pivot finished american

and it works on trickier problems, like the original one at that post:

Original Problem

 
¿Fórmulas? No nos hacen falta fórmulas apestosas!

4 Comments

  1. Darin Myers says:

    I’, with you Jeff. Having a good understanding of formulas is important, but this is a much better approach. With formulas, if you need to answer a different question, you’ll likely have to write a whole new set of formulas. With a pivot table, just pivot the parts around a bit and you have your answer.

  2. Dick Kusleika says:

    Uh oh, the Spanish is coming out. Sounds like wifey is back.

  3. Jeff Weir says:

    Heck no. If she was back, I’d be muertos on account of the strong inverse relationship between blogging and housework. Luckily I’ve got a week left of bachelorhood. I’ill need it, given the house is covered in a perceptible layer of grime, not to mention an invisible coating of what I’m told females call dust which apparently is undesirable. My posting schedule may fall as the hygiene level rises. I’ll whip up a graph.

    No, rather I’m brushing up on my Spanish so I can visit Ecuador. Julian must have some mean tips on how to handle and distribute large amounts of data.

  4. Doug Jenkins says:

    One of these days I’m going to have a good look at these new fangled pivot-table things.

    in the meantime, I offer this nifty little UDF, complete with code tags (which doesn’t even need to be entered as an array function):

    Function Earliest_in_month(RMonth As Long, DataRange As Variant) As Double
    Dim i As Long, EiM As Long, Nummatch As Long
        DataRange = DataRange.Value2
        For i = 1 To UBound(DataRange)
            If DataRange(i, 1) = "" Then Exit For
            If month(DataRange(i, 2)) = RMonth Then
                Nummatch = Nummatch + 1
                If Nummatch = 1 Then
                    EiM = DataRange(i, 1)
                Else
                    If DataRange(i, 1) < EiM Then EiM = DataRange(i, 1)
                End If
            End If
        Next i
        Earliest_in_month = EiM
    End Function

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: