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...

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...

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.

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.

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)!

I need your help!

I thought that I knew a lot about Excel, but the truth is, I can't think of one more thing to share with you. Although if you master the things covered here, you WILL be the Excel Expert in your office. You will shock and amaze your office mates, in general. But, I want to answer more questions. I can't think of anything to answer, though. Will you please ask me a question, friend?

How you found me

Here are some keywords that brought you here. I wish I knew what you were looking for because I think I could help.

Excel Bring Second Value: I don't know what that could mean, but maybe you could try an If statement? Give me more, and I think I can help.

VLOOOKUP: I must have a typo like you. The problem here, friend, is one too many O's in your function. If you figured that out and still need more information, see here:VLOOKUP

Table Array Vlookup: I hope the VLOOKUP post helped in some way.

VLOOKUP Second Result: I don't know, but might I suggest an IF combined with an ISERROR and VLOOKUP, perhaps? See here.

My favorite: vlookup getting col_index_num from a list: You can totally do this, but I don't know why you would want to:
It looks like this:
=VLOOKUP(A7,Data!$A$2:$F$67,Sheet1!A12,0) When you fill in the formula, it moves down the list, too. That was pretty easy to figure out, though, so I'm sure the super user looking for this solution won't be happy with what I've shown him here.

stop copy and paste data validation: Have you tried to copy and then paste special, paste values?

complicated vlookup returning #value: There could be a lot of things to check here. You might try formatting the lookup values on the original worksheet and the in the table array exactly the same. When I'm really struggling and I know there should be a match, I use Data-->Text to columns to make sure everything is formatted exactly the same. Plus, if you can, highlight your lookup values, do a CTRL-F to find all spaces, and then replace all the spaces with nothing. This only works if your lookup values don't have spaces legitimately.

I really am happy to answer questions. It's quite possible I won't be able to help, but if I can, I will be happy to. Click on the Contact Me box, and I will be happy to help you out.

Pivoting the data, Lesson Two

So, you've got a Pivot Table and you think it's OK. But now, you really want to pivot the data.


Put your cursor on Item right there in the pivot table. Drag it to Columns section of your pivot table, where the months currently are. Drop it. Now, you have this:


It's an interesting, but useless, way to present data. So, keep pivoting. Click on Months and drag it to the row section of your pivot table. Now, you have this:


I don't really like this view much, either, but there may be instances when you have applications when pivoting the data like this would be beneficial.

Next time, we can start looking at formatting the data. It was when I started trying to format the data that I realized that PIVOT TABLES<>Excel.

Pivot Tables and why I Love Them

The thing that is so fantastic about Pivot tables is that you can summarize huge amounts of data to help you and your users better understand what your huge amount of data has to say to you. You can also summarize small amount of data, too. And, then once you've summarized your data in a Pivot Table, you can "pivot" the data to look at in a different way.

I used to hate pivot tables. But that was back when they confused me, and I couldn't understand why they were called "Pivot" tables. Now, I get them. I really, really get them. And, I love them. I hope you will, too.

I've alluded to this before. Pivot tables are pretty easy, but they aren't exactly like Excel. I don't know what Microsoft has to say about this, but I personally consider Pivot tables to be an application within an application. If you try to use the data in a Pivot table just like you would the data in Excel, you'll hate Pivot tables. So, before we even start, I want you to commit to thinking of Pivot tables as its own application. Can you do that? Doing that will be the thing that takes you from User to Super-User! Yay, are all your dreams coming true?

Assuming you can commit, let's pretend that you are a flea market/pet shop owner. You have been keeping a list of the items that you've sold and when you've sold them, but so far, that data isn't really speaking to you. It's not telling you a story.


Click on Data-->PivotTable and PivotChart Report to bring up this wizard:


Because this is a basic lesson, let's go with the defaults, and then click NEXT. Excel is so clever, that it knows where your data is and will highlight it for you, as long as it contiguous. For this example, go with what Excel suggests:


and just click next.



Go with the defaults here and click Finish. You can choose where you want the pivot table to go right now, if you are so inclined. Doesn't really matter, though. You can copy and paste the whole thing if you need to move it somewhere else.

Here's what you will see:


Now just start dragging and dropping.

Oh, gosh. Before you start, let me give you some definitions:
Row Fields: Data that will appear in the Rows.
Column Fields: Data that will appear in the Columns.
Page Fields: I don't use this very often. And, I'm not missing out on much, either.
Data Fields: Here's where things get summarized. So, the data field will always be a number. It can be a sum, average, count, min, max, etc. You can put text fields here and then count them. You can put date fields here and count, min, max or average them. Or you can put a number data here and summarize it however you want.

I normally start with the Row Fields first. In this case, I want to see a summary of the items sold. So, click on ITEMS and drag and drop it to the Row Fields Box. Now Drag over Qty Sold to the data box.
Check it out! You've summarized your data by item.


So, you can see that you sold 97 Llamas, but only 21 Llama harness. There's an opportunity right there, I think.

This view is interesting, for sure. But, it doesn't give you any ideas about when your items were sold. Go back to your field list, drag and drop "Date Sold" to the Row Block. Say, now that's interesting.


But, you'd like to see it with the dates across the top. You can do that. Grab Date Sold, either from the field list or the pivot table, and drag it to the Column Block. Now look!


This is not easy to look at, though, is it? Since Pivot Tables are so great at summarizing, you don't want to do all that paging over. Ick. Pivot tables has a solution. Right click on any date in the column headings. Choose Group and Show Detail and then Group.


You get this dialog box.


To make things easy, choose months. You get this view. NICE.


I hope this has been educational. Tomorrow, I'll show you how to actually Pivot that data, really turning it on its side.

Love Pivot Tables.

Click here for the sample data to play with:
Pivot Data