Customize Weekends With Excel WORKDAY Function

Project End Date with Excel WORKDAY.INTL http://blog.contextures.com/

If you're trying to figure out when a project will be completed, you can use the WORKDAY.INTL function (Excel 2010 or later). It's more flexible than the older WORKDAY function – it doesn't assume that you work Monday to Friday any more!

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

Continue reading Customize Weekends With Excel WORKDAY Function

Create a Rolling Total in Excel

Create a 12 Month Rolling Total http://blog.contextures.com/

It's easy to create a running total in Excel, and show the accumulated amount in each row. You can use the SUMIF function in a table, or show running totals in a pivot table.

But what if you want to show the total for a specific number of previous months – not all the previous months?

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

Continue reading Create a Rolling Total in Excel

Hide Formula Results Based on Date

Hide Formula Results Based on Date http://blog.contextures.com/

My challenge this week was to show the running total in a list, but only for the current date and earlier. I wanted rows for future dates to appear empty, but have the formulas in them – ready to go. How would you solve that problem?

Continue reading Hide Formula Results Based on Date

Count Items Based on Another Column

Count Items Based on Another Column http://blog.contextures.com/

How can you count items in one column, based on a criterion in a different column? We've shipped orders to the East region, and want to know how many orders had problems (a problem note is entered in column D). COUNTIF sounds like the right function to use, but it doesn't work for this problem.

Continue reading Count Items Based on Another Column

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.

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.

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:

COUNT COUNTA COUNTBLANK

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

=COUNTBLANK(A1:A5)

More Complicated Counting

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

COUNTIF COUNTIFS SUBTOTAL AGGREGATE

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

Continue reading How to Count in Excel