Excel by Joe

Solutions to your Excel Problems

ABS Function – How to make Absolute values in Microsoft Excel

abs title

This function will give you the absolute value of a number.

abs1

abs2

 

Number – This is the number that you want the absolute value for.  It can be a reference or a number

Column B are the results.  Column C is the formula.

Examples:

In cell B2 – We want to find the absolute value of the number in cell A1.  So we used the formula =ABS(A1).  So it gives you the absolute value of 33.4 which is 33.4.

In cell B3 – We want to find the absolute value of the number in cell A2.  So we used the formula =ABS(A2).  So it gives you the absolute value of -22 which is 22.

In cell B4 – We want to find the absolute value of 11.  So we used the formula =ABS(11).  So it gives you the absolute value of 11 which is 11.

 

 


How to use Solver in Excel to create fantasy football lineups

solver

Solver is a very powerful tool in Excel to come up with optimal results when you have many variables to take into account.  One such issue I ran across recently was in trying to create a perfect lineup for my fantasy football team.  I have salaries and projected scores along with salary and position constraints.

So I made this video below on how to use Solver to do that.  You do not need to understand football.  It is universal.  This video will help explain how to set up your spreadsheet to use solver, how to set up constraints in solver and how to get results.

 

 


How to Count unique values with functions

uniques

A common function many people use is the COUNTIF function and that will count any value you choose.

count_uniques1

In the example above, we counted every cell in Column A that said “Joe” with =COUNTIF(“A1:A10,”Joe”) .  It was 3.

But there isn’t one function to count unique values.  But we can make an array formula with 2 functions to do this.  We start off with this :  =SUM(1/COUNTIF(A1:A10,A1:A10))

But then instead of pressing Enter you press CTRL – SHIFT – ENTER

What this does is to make the function an array.  You need to do this step or else the formula won’t work.

You will get the following when you do that:

count_uniques2

It shows 7 uniques in that range which is correct.

This is a very cool way to get unique values without doing any pivot table or adding any helper columns to find this out.  I hope you liked it.

 

 

 


Trouble shooting a VLOOKUP error with the TRIM Function

vlookup trim

Sometimes VLookup functions don’t act how you want.  It’s not the function that is wrong though, it’s usually the data.  One common error with Vlookups are trying to pull data that doesn’t match because of spaces.  You may have a group of data that has an extra space at the end or the beginning.  It’s just something that happens sometimes because of the way you import or export data.

To get around this issue, we can use the TRIM function.  This function will take all unwanted spaces out of words.  It will still leave the normal spaces where they should be.  However it will fix up the word so that it will then match.

I made a video below to help explain this.

 

 

 

 


Converting Numbers to Text in Excel

title

In this post I will show you how to convert numbers to text in Excel. I will outline 2 steps needed to do this.

convert numbers to text

The first step is through a function called text(). Let’s say we have a column of numbers, see below. We want to convert them to text.

numbers_to_text1

We can use a function called text. Go to cell B2 and insert a function and find the TEXT function.

The first part of the function is the reference to the cell we want to convert.

The second part is the formatting of the cell.

Look at the screenshot below. In column B is the actual formula. Column C just shows what is the formula in column B.

numbers_to_text2

So for the first one, we used the formula: =TEXT(A2,”0.00″)

What this does is take the value in A2 which is 33 and converts it to text which looks like 0.00. So that mask says it should have two decimal spots after the number, so our result is 33.00.

You can see how in row 3 and 4 I use different formatting and you get different styles of results.

The second step to convert numbers to text is through Paste Special.

Now that you have the results, they are still a formula.  To convert to text completely, you need to hilight column B and click Edit-Copy.  Then go to Paste – Special right over the same column B.

It will bring up a wizard with a few options.  Click on Values.

numbers_to_text4

Then press ok.  Then your results will now be text in column B.

numbers_to_text3

 

 

 

 


Using Dynamic Labels in Dashboards in Excel

dynamic labels in dashboards

Dashboards are a great feature in Excel to make your spreadsheets come to life. Recently I have teamed up with a fellow Excel Enthusiast, Mynda Treacy on Excel Dashboards. While I specialize in Functions and Formulas, Mynda is an expert in Pivot tables. While I do have a few dashboard tutorials out there (Excel Dashboard Post, Mynda’s expertise takes it to the next level and I wanted to share this video with you.

She discusses Dynamic labels in dashboards, using GETPIVOTDATA and TEXT functions. It is very well done and will teach you something if you create dashboards frequently. Here is the video:

Mynda also has a Dashboard course which is incredible. There are over 10 hours of videos with tons of handouts, homework and spreadsheets to follow. This course is closed now but will open in Tuesday July 14th. When it opens I will send out a link to everyone that will give them 10% off.

If you want to read up more on her course now, it is at: http://www.mothresource.com/212-19-3-21.html


How to Break apart text without Text to Column

breaktext

Text to Column is a great tool to break apart the contents of cells. However there are easier ways to do it without performing tasks like that. You can use Excel Functions to do the trick. To do this, we will use the following Functions: LEFT,MID,RIGHT, and SEARCH.

Download sample file here

So let’s start off by looking at what we want to split apart:

break_text1

 

In rows 3 to 5, we have some cities with States listed, all seperated by a colon.  If you want to just get the city name, you could use the LEFT function, however we don’t know how many characters from the left side to pull.  The length of the cities will vary.

We can use the SEARCH function to look for the colon though.  It will bring back the character that the colon is on.  So for cell A3, if we did

=SEARCH(“:”,A3) it would bring back an 8.  What this does is look for the colon, which you need in parenthesis.  It looks in cell A3 which is Seattle:WA.  If you count the characters, the colon is in the 8th spot.

So we can now nest that formula in our LEFT function to pull the left most characters.  Since we don’t want to include the colon in our results, we have to subtract 1 from it.

=LEFT(A3,SEARCH(“:”,A3)-1)

So we are pulling the left 7 characters in cell A3, which is Seattle.  Then if I copy it down to the other cities, we get:

break_text2

So even though Boston is 6 characters, Excel knows to search for the colon and subtract 1 and it will return the left 6 characters in that instance.

 

This also works for middle initials.  Say we want to pull out the middle initial for a full name.  The name is Harry S Truman.

Since we are pulling an item from the middle of a cell, we would want the MID function.

First we want to find out what character the middle initial starts on.  So we would use the SEARCH function again but this time we are searching for a space.

 =SEARCH(” “,A8)

break_text3

Excel searches for the space in cell A8 and returns a 6.  It pulls the first space it finds, which would be the space after the first name.  So this would work with any name.

So the middle iniital would be character 7 in this case.  So we just have to add a 1 to the SEARCH.

So now, all we have to do is put in our MID function and insert the SEARCH function into it.

break_text4

 

We use =MID(A8,SEARCH(” “,A8)+1,1)

To explain this, MID looks at cell A8.  Then we need the starting character.  So we inserted our SEARCH function above which pulls a 6.  However that is where the space is, so that is why you see a +1 at the end of it.

Then the final part is a 1.  That tells how many characters to pull.  Sinec we only want the middle initial, we use 1.

 

So there you go.  A new way to pull items from a cell that are not all the same length.  Have a great day!

 


How to Calculate Days Outstanding for Overdue invoices in Excel

feature

Excel can be a great tool to calculate invoice payment information for Accounts Payable registers.  If you are a business that pays a lot of vendors, you can keep track of all your payments and invoices and determine which ones are overdue.  Excel is a great tool to do this.

Let’s say we have this :

accoutns_payable_excel1

Download the sample Excel file here

We have a few invoices along with Invoice date, their terms and when the invoice is due.

We want to calculate how many days these invoices are outstanding and tell if they are overdue.

To figure out Days Outstanding, We need to take today’s date and subtract out the due date.  However we want to make this interactive.  To do that we will use the function TODAY().  This will give you today’s date.  So we can put:

=TODAY()-D2

This will give you days outstanding.

accoutns_payable_excel2

 

To figure out if the invoice is overdue, we can use an IF statement.  We just want to say if the Days outstanding is greater than 0, then we would say it is overdue.  Everything else would mean it is not overdue.  So we can use the formula:

=IF(E2>0,”yes”,”no”)

This would tell us that it is overdue when the days are over 0.  Once we copy it down, we would get the following:

accoutns_payable_excel3

The second invoice isnt due until June 24th and since today is June 8th, this isn’t overdue.  However the first one was.