Contextures

Contextures News 20200114

Strange Excel Formula

January 14, 2020

A strange Excel formula, slow macros, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Strange Formula

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:

  • The minimum bonus is zero (nobody had to give money back!)
  • The maximum bonus is 1500

What formula would you use in the Bonus column, to calculate each person's bonus?

bonus min and max

For each employee, there are 3 possible results, 0, 1500, or AmtA. I've sorted each set of numbers, from small to large:

  • Emp1: (375), 0, 1500
  • Emp2: 0, 1200, 1500
  • Emp3: 0, 1500, 1500
  • Emp4: 0, 1500, 2775

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:

  • =MEDIAN(B_Min,B_Max,C8)

MEDIAN function calculates bonus

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.

On Contextures

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.

convert number to written words

Excel Articles

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?

Also see: My Excel Products || Previous Issues

Slow Stitching

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.

weekly photo

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.

Debra Dalgleish
dsd@ contextures.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

contextures newsletter info

 

Related Links

Averaging functions

AVERAGE, MEDIAN and MODE

Slow Macros

FastExcel review

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: January 12, 2020 4:16 PM