Option Button Scores With CHOOSE Function

Option Button Scores with CHOOSE Function http://blog.contextures.com/

You can use option buttons on a worksheet, to make it easy for people to pick just one item from several options. There's no programming required – just set up a group box, add option buttons, and link the buttons to a cell. The video at the end of this post shows you those steps.

The option buttons send a value to their linked cell, and we'll take a look at how you can use those numbers.

(adsbygoogle = window.adsbygoogle || []).push({});

Continue reading Option Button Scores With CHOOSE Function

Beware the Nested IF Formula in Excel

Beware the Nested IF in Excel http://blog.contextures.com/

Do you use the IF function in your workbooks?  It's helpful if you want to check something, and then show a result based on the result of your test.

But, things can go horrible wrong if you try to do too much with IF -- sometimes other functions do a better job.

(adsbygoogle = window.adsbygoogle || []).push({});

Continue reading Beware the Nested IF Formula in Excel

7 Ways to Sum in Excel

7 Ways to Sum in Excel http://blog.contextures.com/

What was the first function that you learned in Excel – if you can remember that far back?

I'll bet it was the SUM function. There's an AutoSum button on the Ribbon that makes it very easy to put a total – just select a blank cell below a column of numbers, and click that button.

Continue reading 7 Ways to Sum in Excel

Compare Excel Worksheet Values and Formulas

Compare Cell Values and Formulas in Excel http://blog.contextures.com/

If Excel sheets are set up identically, you can create 3-D formulas, to sum a specific range, in a group of sheets. (You can use other functions in a 3-D formula too, such as Average, Count, Min or Max.)

For example, this workbook has sheets for the East, Central and West regions, and all three sheets are set up the same.

Continue reading Compare Excel Worksheet Values and Formulas

Counting in Excel

7 Ways to Count in Excel http://www.contextures.com/xlFunctions04.html

The most popular function in Excel is SUM, based on my experience. That's probably the first function you learned how to use, and in the old days, it was the only function that you could insert with the appropriately named AutoSum button.

Sometimes we overlook how much counting we have to do every day too. Here are a few scenarios that you might encounter at work (or should I say "en-count-er"?):

Sales: number of orders for a specific product, from a list in a worksheet Human Resources: count of employees with 10+ years employment Shipping: number of shipments pending,

Continue reading Counting in Excel

How to Count in Excel

Learn 7 ways to count in Excel, with function tips and tricks http://blog.contextures.com/archives/2015/03/26/how-to-count-in-excel/

There are lots of different ways to count things in Excel – maybe you need to count the numbers in a column, or all the data, or just the blank cells. Fortunately, there is a function for each of those:


For example, to count the blank cells in the range A1:A5, use the following formula in cell A7:


More Complicated Counting

If you have more complicated things that you need to count, there are other functions to do the job:


For example, to count only the visible numbers, after filtering and/or

Continue reading How to Count in Excel

Ignore Outliers with Excel TRIMMEAN

Excel TRIMMEAN function http://www.contextures.com/excelaveragefunctions.html

From what I've seen in workbooks over the years, SUM is the most frequently used Excel function, and AVERAGE is the runner-up. Would you agree, or do you see other functions used more often than those two?

Last week, a client asked about excluding some of the highest and lowest numbers from a data set, to give a better average. It looked like some of those outliers had been incorrectly entered (an extra zero, or a mistyped numbers), and they were throwing off the results.

So, we did some tests with the TRIMMEAN function, to compare the results with a

Continue reading Ignore Outliers with Excel TRIMMEAN

Football Fun in Excel

This Sunday is Super Bowl XLIX – the last one that will start with "XL".  Next year, it will be Super Bowl L – that doesn't exactly roll off the tongue!

In 2011, I showed how you can use the ROMAN function in Excel, to change a number into a Roman numeral. That year, they were playing for the 45th time, which was XLV.


Other Football Functions

There are other Excel functions that sound like they could be used in a football game. Here are a few, and I'm sure you can think of others:


Continue reading Football Fun in Excel

Count Activities in a Date Range

guests date range http://blog.contextures.com/

Today's challenge is to count how many guests stayed at a hotel, in a specific date range, based on the guest arrival and departure dates.

Continue reading Count Activities in a Date Range

Create Random Text in Excel

create random text in Excel http://blog.contextures.com

Last week, I was creating an Excel file with sample data, to use for a few experiments. But don’t worry, they weren’t mad-scientist-type experiments – I was doing Power Pivot experiments, and needed some data to play with.

I needed 2 types of data:

Numbers: sample test scores in one column Text: random Region names and Gender in other columns.

Continue reading Create Random Text in Excel