What are the smallest and largest numbers that you instantly recognize as years?

Earliest : 1066 or perhaps 1054, both mentioned above.
Latest : 2030 ? Anything after date is too far away for me to really see it as a date, although 2525 is a fun example.

Lowest: 1054, the year of the Crab Supernova.

Highest: 3001, from the Clarke story.

Time was, I did not expect to live to see the century turn over. I very seldom write checks, but when I do, I have to cross out the ‘19’ pre-printed in the date field.

I recall seeing matching tombstones with a ‘19’ pre-carved … and with the surviving spouse outliving his die-by date. :smack:

The only pre-1066 date I’ve memorized is Charlemagne’s coronation in 800 — a round number easy to remember. I’ve wondered if the off-by-four error in Anno Domini was due to Charlemagne’s insistence on a round number for his coronation year! :smiley:

When I see “44” my brain adds “BC” to get the year Julius Caesar was assassinated.

When I see “753”, I automatically parse it as the (mythological) year of Rome’s founding (BCE, that is). I can’t think of any upper maximum, although anything of the form 20xx I’d recognize as a year.

venerable SUM function, except they return the single smallest or largest value, respectively, instead of adding up values. The SMALL and LARGE functions work in a similar fashion, but with an extra argument:



In these functions, array is a range of cells, and k is the nth value you wish to return. As shown in Figure 2, =LARGE(B2:B11,2) would return 872 as the second largest value, while =SMALL(B2:B11,3) would return 266 as the third smallest value. By way of comparison, the following formulas would both return 958 and 191 for the largest and smallest values, respectively:





Figure 2: LARGE and SMALL return the nth values from a given list.

If you’re creating a list of the top or bottom 10 values, it can be tedious to manually edit each LARGE or SMALL function with the proper value for the k argument. To save time, I use the ROW function, either inside the LARGE or SMALL function or in a separate column. The ROW function returns the row number for a given cell. If you enter this in cell D2, Excel will return 2:


In Figure 3 you can see that I entered this formula in cell D2:


In this case, ROW() would return 2 because it’s entered on the second row, so subtracting 1 changes the result to 1. Alternatively, I could provide the address of a cell in row 1 of the worksheet:


Figure 3: These formulas are the basis for creating a ranked list without re-sorting the source data

In cell F2, I entered this formula:

=LARGE(B$2: B$11,D2)

Depending upon my needs, I might have used this formula instead:

=LARGE(B$2: B$11,ROW()-1)

The dollar signs in the formula instruct Excel not to change the row numbers when I copy the formula down. The last bit of information that you’ll likely want is to associate a name with the values that you’ve isolated. To do so, you can use the MATCH and INDEX functions together in cell E2:


I’ll explain MATCH and INDEX in more detail in an upcoming article, but for now the short answer is that in this case MATCH is determining which row a sales figure amount is on, and then INDEX returns the corresponding text from column A. This is akin to VLOOKUP, but with the flexibility of being able to look up data from the left, which VLOOKUP can’t do without making a special provision.

There’s one caveat to this approach that you should be aware of. If the same value is on your list twice, then MATCH/INDEX will return the corresponding name twice. Next week I’ll describe how you can use the COUNTIF function to create a tiebreaker that will give you a unique value to match for each item in the list.

In any case, once I have the formulas in cells D2 through F2 in place, I’m able to copy the formulas down as many rows as needed without any additional modifications, as shown in Figure 4. Do keep in mind that if you drag too far, LARGE or SMALL will return #NUM!.

Figure 4: If you drag the formulas in cells D2:F2 too far, LARGE will return #NUM!.

Thank you

Spam reported. (swaragh)

Probably 1492. Probably nothing past the 21st century would I “instantly” associate as a year.

1066 Battle of Hastings was so drilled into me growing up, that I can’t see anything else when I see those numbers. (Not quite true–I lived somewhere for a couple of years where my postal code was 1066, so it did displace some of the Hastings associations over time.) I can’t think of any numbers before that with such a strong association. Highest is much tougher. The 2525 mentioned before works for me. Maybe 3001 from the Arthur C. Clarke series.

There are plenty of dates prior to 1066 that I know. But if I saw 800 written on a page, my first thought would be of it as a number rather than as a year.

Think of it as Jeopardy. If the screen shows 800, my question will be “What is two times four hundred?” not “What year was Charlemagne coronated?” But if the screen shows 1066, my question will be “What year did the Normans invade England?” not “What it two times five hundred and thirty-three?”


10,191 was the year the Atreides moved to Arrakis. Have no idea if it correlates with Earth years, however.