Eric P. Butts

THE Career Site for Accountants and Consultants.

You are here: Home / Archives for excel formulas

by Leave a Comment

The Excel Tip You Didn’t Know You Needed Until Right Now

As you know, I like to dabble in a little spreadsheet every now and again.

Tonight I stumbled upon a gem that I had to share.

I was working on a spreadsheet containing a handful of items to auction off ebay style. I had a fair market value (fmv) in one column and wanted the starting bid to be half of the fmv. There was just one problem…only nutjobs start bids at weird numbers like $12, or worse $12.50.

Enter my newfound friend – “ceiling.math”. After a little bit of googling and dodging a landmine of adsense and pop-up ads (my computer is currently revolting), I found this little guy and learned it can round up to the nearest multiple of basically anything.

As in ceiling.math($12.50,5) = $15. Don’t you think that’s much more satisfying to look at? If you need a little more precision and are dealing with something like hourly billings, this can help you there too. For example, if you always round up to the nearest half hour on timesheets for billing clients, ceiling.math has you covered, as in ceiling.math(11.25,.5) = 11.5. Simple.

If you’re swimming in spreadsheets everyday and like little nuggets like this, you’ll want to check out my full Excel training so you can get some high-impact efficiency gains in how you work in Excel.

https://ericpbutts.com/excel-bootcamp

by 4 Comments

How to Use Excel Wildcard With SumIFs Formula

Excel Wildcard and Sumifs work wonders together.
Click to see the wildcard functionality in action.

Do you know about the Excel wildcard functionality?

You know, the one where you put an “*” before and/or after something you’re searching for and Excel executes a search based on what you input. For example, imagine you have a spreadsheet with a lot of data. Maybe it’s an export of transaction activity for the month. You have hundreds of account numbers ranging from 1xxx through 9xxx. You want to spend a little extra time on the 8xxx accounts because that’s where the bulk of activity gets posted. You can do one of two things:

Option 1: Add a filter, go to the filter drop down and unselect all accounts, then scroll down to the accounts you want to look at and manually check them.

Option 2: Add a filter, go to the search in the filter drop down and type in “8*”. Not only is option 2 faster (Under option 1, you could be scrolling for days through a bunch of accounts that show up because of one row of data in your file.), but it’s also more reliable. Using a manual approach, you could easily miss a box that you meant to check and skew your analysis.

Pretty sweet, right? And it gets better.

You can also use Excel wildcard functionality in formulas, and I’m going to show you how with another example. Say you have this small table of data:

Use Excel wildcard and sumifs to save yourself some time

Note: “X” represents contractor and “E” represents employee in the grade column and the number represents level with the respective contractor or employee category. Now, let’s say you want to use formulas to summarize a few different pieces of information:

1. Salary paid to employees and contractors, respectively

2. Salary paid to employees and contractors with the last name “Brown”

3. Salary paid to employees whose names contain “Brown” in them.

How would you attack this problem? Looking at number 1 above, you might be tempted to just add a column to flag each employee as a flag and then use a sumif formula, but that strategy quickly becomes cumbersome and impractical as the number of extra columns needed in your dataset increases.

This is where the Excel wildcard function comes in handy. Here’s how to take down each of the above:

1. For salary paid to contractors, type =SUMIFS(E4:E11,D4:D11,”*X*”). For employees, do the same thing but replace the X with an E in the formula.

2. For salary paid to contractors with the last name Brown, type =SUMIFS(E4:E11,D4:D11,”*X*”,C4:C11,”*Brown”). Again, for employees, do the same thing but replace the X with an E in the formula.

3. For salary paid to contracts with the last name containing Brown, type =SUMIFS(E4:E11,D4:D11,”*X*”,C4:C11,”*Brown*”).

You notice the slight difference between 2 and 3? Trust me when I tell you adding this functionality to your repertoire will save you a lot of time. There is one other formula I used to put this example together.

Click the button below for the video or join my free content library and then navigate here.      

        Click Here to See Excel Wildcard in Action