Wednesday, November 5, 2008

Mid

MID is very similar to LEFT and RIGHT in that it strips specified characters out of a string of text or numbers.

So, let's use the example we used yesterday:
110308/20/7R13504

Here, we know that the 20 in the middle represents a useful piece of information, like warehouse or factory or the number of pieces you need to produce. But, we also know that it's not useful stuck in that long string.

It is pretty simple to extract:
=MID(A2,8,2)

The formula says to go over 8 characters (including spaces) and return two characters starting with the 8th character.

The result of this formula is:
20

This is pretty easy stuff once you know about it. Definitely another good tool to have in your guru belt.

Tuesday, November 4, 2008

LEFT, RIGHT...Just Vote!

Let's say you've run a report and downloaded it to Excel. Let's also say that this report doesn't have a need by date field, but the need date is part of a reference code that looks like this:

110308/20/7R13504

Let's also say that you want to do a pivot table where all your records are summarized by need by date and you are OK with the format mmddyy (for now).

Here's what you do:

=left(a2,6)

This formula will return this result:
110308

The RIGHT function works the same way:
=right(a2,7)

Will return this result:
7R13504

These are definitely nice functions to have in your toolbox. And, there is more. I'll look at MID tomorrow.

Oh, and if you're in the US and you're on the left or on the right, I don't care. VOTE TODAY!

Monday, November 3, 2008

OR

I am going to try and keep this short and simple and to the point. If you knew me at all, you'd know that's a challenge, but I'm really going to try!

OR is a function like AND and ISERROR in that it returns a TRUE or FALSE result. So, like AND and ISERROR, OR is pretty worthless unless you couple it with an IF. It's like a Peanut Butter and Jelly sandwich without the bread.

If you create a formula like this:
=OR(B2="A",C2<=D2)
You will either get a TRUE or a FALSE result.

If that result is TRUE, you can use an IF statement to have it return a specific value, a result of a formula, or the value in a nearby cell. Or, 60 other things you might come up with. Here's an example where if at least one argument in my OR statement is TRUE, the formula returns the value found in H2. Otherwise, it returns a value of blank.

IF(OR(B2="A",C2<=D2),H2,"")

Regarding syntax, please note that your arguments in the OR statement are simply separated by commas. I often try to sneak one of these () in there, but that's just silly. Commas only. (The same is true for AND).

Finally, your OR statement is limited to only 30 arguments. Just 30. 31 is 1 too many, OK? (The same is true for AND).

Just in case I didn't make it clear enough, and because I think I've made my posts on AND and ISERROR too long and complex: OR, AND, ISERROR are all similar in that they all return TRUE or FALSE results and don't make much sense if you don't use them in conjunction with IF.

Thursday, October 30, 2008

Page Setup Interface and printing the prettiest report in the office

These aren't all really tricks, but I used Excel for a year or two before I figured them out or learned them through "tribal knowledge."*

When you click File-->Page Setup, you will get this interface.


The thing I like to change here, and I've mentioned it before, is to change Scaling to Fit to and then choose 1 pages wide by BLANK tall. Do this and Excel will scale it so that it's not too wide for one page and will use as many pages as it needs.

I rarely do anything with Margins, so I won't cover it here. Basically, if you need to mess with Margins, do it here.

Header/Footer is very nice:

You can add a header and a footer to print on every page of your report here. Click the pull downs and see some existing options or choose the custom header or footer buttons to get this interface


Here's a tour of the buttons;

The first button allows you to change the font in your header or footer.

The second button shows the current page number and the third button shows the total number of pages in the report. I like to hit the button for page number type in the word "of" and then hit the button for total number of pages (Page 1 of 20)

The fourth button shows the date and the fifth button shows the time. Here, I like to show the date, hit enter and show the time below the date. Not sure why. I just do.

The sixth button shows the Path and File. This is really nice if you've buried your file in a complex file structure and need to find is 6 months later when the CEO realizes that it's the best report EVAH!

The seventh button shows just the file name. The eighth button shows the tab name. (For the love of VLOOKUP, If you use this, change the tab name from Sheet1!)

The eighth button allows you to insert a picture. A logo for example, you Excel fancypants.

That last gray button is grayed out. I'm not sure how to un-gray it.

The point with Header and Footer is don't try to make your own on the spreadsheet itself. Do it here, instead!

Finally, we have Sheet. I like this tab of the Page Setup Interface best:


Under Print titles, if you define rows to repeat at top, that row will repeat on each printed page of your report. Same is true for columns! This will make the report so easy to work with for those people who like printed copies. Do this for them!

Under print, I always check Gridlines. Because I like my gridlines to print without giving everything a border in the cell format.

I normally don't use anything else, so I won't bore you with the details because if you've gotten this far, you're a super champ as it is!

Good day!

*Does my corporate America speak impress??

Wednesday, October 29, 2008

IF and AND, together

If statements are a pretty powerful tool in your Guru belt. If you know IF, you probably know more than 50% of the people in your office. We've used IF here in conjunction with ISERROR to format your spreadsheet nicely. Today, I want to use IF with AND because I really like these two functions together.

Let's say you work for a company who only pays in increments of 15 minutes. So, if you show up at 2:03, you start getting paid at 2:15. And, if you leave at 5:03, you stop getting paid at 5:00. I know! Corporate America*, right? Geez.


Well, if you work in Corporate America and you're punching a time clock, all of that is already worked out in the payroll software, so you wouldn't really have any need to do this exercise. But, it's the best example I can think of...

The End Result:


Here's the formula that will do it:
=IF(AND(MINUTE(C2)>45,MINUTE(C2)<=59),TIME((HOUR(C2)+1),0,0),IF(AND(MINUTE(C2)>30,MINUTE(C2)<=45),TIME(HOUR(C2),45,0),IF(AND(MINUTE(C2)>15,MINUTE(C2)<=30),TIME(HOUR(C2),30,0),IF(AND(MINUTE(C2)>0,MINUTE(C2)<=15),TIME(HOUR(C2),15,0),C2))))

Well, as they say in corporate America, how do you eat an elephant? One bite at a time! So, let's take this in little elephant chunks.** (Who eats elephants, anyway?)

=IF(AND(MINUTE(C2)>45,MINUTE(C2)<=59),TIME((HOUR(C2)+1),0,0)

The IF Statement is:
IF(AND(MINUTE(C2)>45,MINUTE(C2)<=59)
IF this is true: the minute is greater than 45 AND less than 59
Return this value:
TIME((HOUR(C2)+1),0,0)
What a neat new function! With the TIME function, the first value represents the hour (in this case my starting hour + 1), the second position represents the minutes, and the third value represents seconds.

If my IF statement was FALSE, return this value:
IF(AND(MINUTE(C2)>30,MINUTE(C2)<=45),TIME(HOUR(C2),45,0)

Another IF statement! This is called Nesting IF statements, and you can only nest 7 IF statements in any one formula. But, you can add ANDs and ORs and test more than 7 conditions. Get creative, play with it!

In English, this says if my minute value is between 30 and 45, return the TIME of the hour of my original start time and 45 minutes.

If the value is false, another IF Statement! I think you get the drift. At the end, if none of the conditions are met in the 4 IF statements, then it will return the value of the original start time. If the formula is written right, it should always be the top of the hour so it falls in line with all the Corporate America BS guidelines. I hate that the man is always out to get me!

I did another formula for the end time:

IF(AND(MINUTE(D2)>=45,MINUTE(D2)<59),TIME(HOUR(D2),45,0),IF(AND(MINUTE(D2)>=30,MINUTE(D2)<45),TIME(HOUR(D2),30,0),IF(AND(MINUTE(D2)>=15,MINUTE(D2)<30),TIME(HOUR(D2),15,0),IF(AND(MINUTE(D2)>=0,MINUTE(D2)<15),TIME(HOUR(D2),0,0),D2))))

I actually just dragged over the original formula, and then changed things up a bit so that the workers don't get paid for any minutes they work past the quarter hour until the next quarter hour begins. Take a close read and if you need the English version, let me know...

*I kid about Corporate America! I love Corporate America! Corporate America loves me! I've never seen such a punishing time clock in Corporate America.
**I kid again! I love elephants, but I'm not so sure they love me back.

Tuesday, October 28, 2008

Grouping without the subtotal wizard

When you use the subtotal wizard, your data is grouped, and you can use buttons to expand and collapse your group:
Expanded:


Collapsed:


Read more about subtotals here

You can also group data in Excel without using the subtotal wizard. I had an occasion to do this just the other day, in fact.

All you do is highlight the rows of data that should be grouped together. Then, choose:
Data-->Group and Outline-->Group

Repeat as necessary for the different data groups you want to create.

Why would you want to do this? I'm not sure. But, I did it to separate the top 90% of my purchased items from everything else. I could have assigned them all an ABC Code (A=top 90%, B=90-95%, C=95-100%) and added subtotals, but I didn't really want to do that, and I didn't need the subtotals. This was quick, easy, and very functional in this application.

Pretty Neat Trick!

Monday, October 27, 2008

Rank and Count, now together in one formula!

RANK is a nice function. You can use it to see where an item falls on a list. You can get that same information by sorting the list, but there will be times when another sort takes precedence, and RANK will save the day.

Start typing in the formula in the formula bar
=Rank(
and then hit the fx to bring up the Rank Wizard box
(there's other ways to do this. Pick the one you like best.)

Here's the wizard box.


The number is the value that you want to rank. The Ref is the range of numbers that are being ranked. Be sure to make this absolute using the dollar signs or your range will change when you copy and paste your formula down. The order specifies order. If you leave it blank, it assumes 0 which is descending. If you choose 1, it is ascending. In other words, the largest number in the list will be ranked number 1 if you choose 0 or leave it blank. The largest number in the list will be ranked last if you choose 1.

If the list includes values which are the same, they will have the same rank.

Assuming that a rank is only important in relation to the number of values, I like to add a count.

In this instance, I want to count values in a spreadsheet that I will be adding to over time. So, instead of counting an absolute list such as $B$2:$B$67, I'm counting the whole column. My count formula looks like this:
=COUNT(B:B)

If I wanted to count an absolute list, it would look like this:
=COUNT($B$2:$B$67)

When I put the rank together with the count the formula looks like this:
=RANK(B2,$B$2:$B$68)&" of "& COUNT(B:B)

I used the "&" to concatenate the two functions together with the text string " of ".

The spreadsheet looks like this:


Putting the two formulas together means that I can get rid of columns G and H entirely. I just left them in to show my work.

I don't use Rank very often. I do use COUNT pretty often. I rarely use the two together. But, when I've needed them, it sure has been nice...

Saturday, October 25, 2008

When Grouping by Dates in Pivot Tables won't work

I started telling you how much I loved Pivot tables, and then abandoned my blog. I apologize.

After I discovered that you can group by dates in Pivot tables, I also realized that sometimes it didn't work. It took me awhile, but I finally figured it out. Basically, if your data set includes blanks, the Pivot table is unable to Group By Dates. Why? Because Blank is not a valid date. I'm hoping Microsoft fixes this in the next iteration. They may already have, but I don't have that version of Excel, yet.

Let me walk you through why you would want to include blank rows in your data set, how to get rid of the blanks in your pivot table and how to group by dates in your pivot table without using the group by function.

There are times when I create a Pivot Table when I know that my source data will not always be $A$1:$C$67


Knowing that I will continue to add to my source data, I choose A:C as my Pivot table. This would happen if you, as a flea market owner, intend to continue to add rows to your original source data as you continue to sell items. You could use the pivot table wizard to redefine your data set every day, or you could expand your data set and just refresh your pivot table as needed.


Now, you have a row for (blank) in your pivot table. Bleh.


And, you don't really want that because it serves no purpose in this application, so you get rid of that blank line by clicking the pull down on the item column, scrolling through until you find blank, and then un-checking it.


But, now your nicely summarized months are gone, replaced by specific dates because a blank is not a valid date format. What was once nice to look it:


Now makes you cringe and frown.


When you try to resummarize it by month following instructions provided in my post Pivot tables and why I love them, instead of getting the nice box where you can choose Months, years, hours, quarters, etc, you just get one group called Group 1.

But, you've reached the conclusion that you will continue to add to your original data set and it's not feasible to redefine the data set every time you want to see fresh data.

Here's what you do!
Go back to your original data set and add three calculated fields. One each for Month, Year, and Quarter


The calculation for month is above. The calculation for year is pretty straightforward:
=YEAR(C2)

The calculation for Quarter is more involved:
=IF(OR(D2=1,D2=2,D2=3),1,IF(OR(D2=4,D2=5,D2=6),2,IF(OR(D2=7,D2=8,D2=9),3,4)))

In English, if the month is 1, 2 or 3, the quarter is 1. If the month is 3, 4 or 5, the quarter is 2. If the month is 7, 8 or 9, the quarter is 3. Otherwise, the quarter is 4.

I like that calculation. They're may be a different way to get at quarters, but this works. You could also change the 1 to "Q1", etc, if you want to get fancy.

Now, go back to your pivot table, right click and choose "Pivot Table Wizard."


It will start you at Step 3 of 3, and you are redefining your data set here and you need to back to step 3. So, choose Back. You can either drag your cursor from A to F, or just type it in manually:
Data!$A:$F

Now, choose next and then finish. Now, in your field list, you have the newly created calculated columns:


Drag and drop years, months, and quarter to summarize the data the way that you like. Change up as needed.


Do those odd column totals bug you? Me, too.

Hover your mouse over those year total columns until you see a black arrow, then right click. This will highlight all year total columns. Now, right click and choose hide.


If you want to get it back, right click on the word, "YEAR" and choose field settings.


Under subtotals, click automatic. You can also choose sum (same at automatic), count, average, etc. Or, a combination of any or all of those in the option box. Use this wisely, people. You don't want your pivot table to get too complex or you'll lose your users which may just be yourself and it's no good to lose yourself over a pivot table.

So, now it's looking pretty OK.


But, you probably want to format it. So, hover your mouse on one of your category totals. Once you get that hover arrow, left click to highlight all category totals as shown in the image above. Now, right click and format as you see fit.

That was a little more than When Grouping By Dates in Pivot Tables won't work. But, what can I tell you. I get wordy...

Wednesday, October 22, 2008

Time Calculations

I was working on a timesheet in Excel the other day, and I was surprised to see that 4:00 PM minus 2:00 PM doesn't equal 2 hours. Very surprised indeed...

So, you know what I did? I hit F1. F1 and my husband and my friend Dawn are the three reasons I am the excel guru that I claim to be.

F1 was very helpful, but I took what it told me and expanded my new found knowledge to get it to do what I actually wanted my timesheet application to do. What I wanted my application to do was calculate the elapsed time in hours between a start and end time in one 24 hour period (as in, not to exceed a day).

Here's what I wanted to see:

From             To                 Elapsed Time in Hours
2:00 PM        4:00 PM             2

When I subtracted 2:00 PM from 4:00 PM like this =(B2-A2), here's what I got:
From             To                 Elapsed Time in Hours
2:00 PM        4:00 PM             .0833
When displayed as time, .0833 = 2:00 AM

So, I think I get what it's doing, but it's not what I want.

F1 suggested a few functions, including the hour function. So, I changed my formula to this:
=HOUR(B2-A2)

And that converted .0833 to the number 2. Which was precisely what I wanted in this particular instance.

So, I pulled my formula down, and I discovered that when my start time was 2:00 and me end time was 4:45, my result was still the number 2.

I hit F1 again, and finished reading the help article. It was then that I discovered the MINUTE function.

Using the minute function on this data set:
From To
2:00 PM 4:45 PM
=MINUTE(B2-A2)
Yields this result:
45

So, I put the two functions together in this simple formula:
=HOUR(B2-A2)+(MINUTE(B2-A2)/60)

It now shows me the difference in hours plus the difference in minutes divided by 60.

From                 To                 Elapsed time in hours
2:00 PM            4:45 PM              2.7500

Easy, peasy puddin' pie.

Just keep in mind, this will not work if the time spans more than 24 hours.

Sunday, January 27, 2008

Recent Keyword Activity

I have to say that the questions I have rec'd here, though few, have humbled me. I still think I know excel better than most people I know. But, it seems the people who find me know more than me. Still, let me respond to some recent Keyword Activity...

how to concatenate and keep spaces
Put them in quotation marks. For example:
=Concatenate(a1," ",b1," and then finally ", c1)
IF
A1=CAT
B1=BAT
C1=SAT
Result=
CAT BAT and then finally SAT


keep table array when pasting vlookup formula
Make the table array absolute using $. For example:
=VLOOKUP(a1,source!$A$1:$B$500,2,0)
Whenever you pull down the formula, the table array will remain absolute at $A$1:$B$500.

how to make excel look at a date as text
where the value in A1 is a date, use this formula;
=text(a1,"mm/dd/yyyy")
You can also use m/d/yyyy or mm/yyyy or yyyy/mm/dd or yyyymmdd or whatever date format you want. But, whatever the format you choose, it is now TEXT and will not change to a number if you change the format. Well, you just can't change the format. It's a text field now. The original data, however, isn't a text field. It's still whatever it started out as.

Cool tip of the Day: From the kicking it old school old school of thought, if you want to make any cell absolute in your formula without typing the $, highlight the formula and hit F4.

Tuesday, January 8, 2008

Helping You Out, part 2

Anonymous said on 1/8/08:


I am in desperate need of help. My firm has a special toolbar they use to make sure all reports we generate are formatted in a specific way. I think it must involve a lot of custom formats because now I keep getting an error message saying "Too many custom formats" whenever I try to do anything. I tried deleting some of the custom formats but that doesn't seem to work. Any ideas?



This one is tough because I think the problem is probably the code that formats your report.  It's probably in Visual Basic for Applications, and your IT group would have to trouble shoot the problem.  Can you tell if the error message is from excel or from the "special toolbar"?


I do have one suggestion, though.  If you try this, please save a copy of your work and perform these steps on the copy, so that you don't lose any of the work you have done so far.



  1. Select all entire worksheet (one easy way to do this is to hit shift-ctrl-down arrow-right arrow from cell A1)

  2. Select Edit-->Clear-->Formats

  3. Now, rerun the script on the special toolbar.


What I'm trying to have you do is clear all the formats so the macros in the toolbars can have an easier time doing their thing.  Without knowing anything, I would guess that you might be working on a sheet that has a lot of formats maybe even in cells that don't contain data.  Clearing out all the formats might all the scripts on the toolbar to do the work it needs to do.


I hope this helps. I suspect that it may not.  If you try, though, please do it on a copy!

Helping you out!

Question:


Anonymous said on 1/7/08:


"I have two fields that I need to compare and bring back data in a third field if they match.

Basically, it's a simple VLOOKUP equation.
But, these two number fields (which are currently both formatted as text) don't always bring back the data, unless I 'retype' the data, then it recognizes that they are the same.
Can you think of why I would need to retype the exact same data for it to recognize it?

I'm bringing in the data via copy/paste, but using field format options to ensure they're both the same."


You're using the field format option to ensure they are both the same, and that is definitely the right thing to do. You may just have to take it a step further.


OPTION 1


When you bring in the data via copy/paste, choose Paste Special and paste as values.  If that doesn't work, go back to the original lookup value, select that column, copy it and paste it special as values (don't paste it somewhere else.  Paste it right where it is).


OPTION 2


If the steps in Option 1 don't work, take it to the next level by formatting the fields so they are exactly the same using the Data-->Text to columns wizard.



  1. Select the column that contains the lookup value.

  2. Click on Data-->Text to Columns.  This will bring up a "Convert Text to Columns Wizard."

  3. The first step is to choose whether your data is "delimited" or "fixed width."  Choose delimited here.

  4. Second step is to either choose the delimiter or the width of your text.  Make it as long as your longest data.

  5. Third step is to choose the data format.  Make it a number or a text, or whatever makes the most sense to you.


Now, select the first column in your table array and repeat the steps, ensuring that you set up this column of data precisely the same way you set up your lookup value.  You may need to play with the options in each of the three steps.  Choosing delimited always worked for me.  But, that might not work for you.


OPTION 3


Another thing that may be hinking you up are spaces.  If your lookup values should not contain spaces, you can do this:



  1. Select the column that contains the lookup value.

  2. Hit Ctrl-F (or Ctrl-H to save step 4...either works exactly the same).

  3. Enter a space in the "Find What" box.

  4. Hit the replace tab.

  5. Enter nothing in the "Replace with" box.

  6. Hit "Replace All"

  7. Repeat these steps the first column in your table array.


The first option might do the trick.  The second option should work all the time (I hope.  It's always worked for me.)  The third option is a little easier, but you have to be careful and you can't use this if your lookup data is suppose to contain spaces.


Let me know if you need pictures, and let me know if this works (or not)!