Strange Excel Formula
January 14, 2020
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
Last week, Jonathan Cooper sent me an example with a strange use for an old Excel function. Well, it was strange to me - I'd never seen the function used like this before. Thanks, Jonathan!
Jonathan had to calculate final bonus payments, based on earlier calculations (AmtA). There are 2 rules:
What formula would you use in the Bonus column, to calculate each person's bonus?
For each employee, there are 3 possible results, 0, 1500, or AmtA. I've sorted each set of numbers, from small to large:
Interesting -- in each case, the middle number is the amount that should be used as the final bonus calculation. And which Excel function returns the middle number, in a set of numbers? MEDIAN.
So that's the function Jonathan used. Here's the formula in cell D8:
Have you see MEDIAN used like this before? What formula did you use to calculate the bonus?
Read more about MEDIAN on the Microsoft site. I also enjoyed this article that compares the median and mean (average) functions. On my Contextures site, see Averaging function examples, and my blog has an article on AVERAGE, MEDIAN and MODE.
Here are recent items from my Contextures site and blog.
Slow Macros: Excel macros ran really slowly in a workbook that someone sent to me. The problem was caused by an Excel custom function (UDF) that ran for no apparent reason, and slowed other macros down. See what I did to make things a bit faster.
Here are a few Excel-related articles that you might find useful or interesting.
Charts: Jon Peltier takes a look at confusing Excel bar charts, and suggests other ways to present the data (Level-Int)
Functions: Jon Acampora shows how to use the XOR function (Excel 2013 or later), to check if people are in or out of the office. I don't see it mentioned, but Jon's formula should be array-entered, with Ctrl+Shift+Enter. There's a video too, so perhaps it's mentioned there. (Level-Int)
Excel Humour: See what people said about Excel recently. Do you need an Excel tracker to keep track of all your Excel trackers?
Lately, I’ve had an urge to do some hand sewing, and serendipity led me to Liz Kettle’s site. She makes small fabric collages with "slow stitching", as a way to relax every day. The goal is to just get it done, without trying to be perfect. It looked like fun, so I tried one, and yes, it’s certainly imperfect! Sometimes we need that attitude when we're working in Excel too.
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20200114ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: January 12, 2020 4:16 PM