Rank

4.98K viewsFormulas and Functions
0

This is driving me crazy.

Here is the data:

NY LV
Jan 15 16
Feb 52 18
Mar 45 24
Avg 37 19
Rank

I am trying to rank the cities based on the average value calculated. I am using the rank formula wrong. Can someone tell me the right way? The file is attached. Tks!

Carl

0

Mike,

Thanks so much! With your help, I was able to put out the report before heading out for turkey. I wanted to let you know that it may not seem like it, but every time you post an answer, I am learning alot, so thanks for that.

Carl

0

Hello Carl,

No in-depth tutorial available as of yet on this subject but it is potentially a good topic. I will consider this.

In the meantime, I did two things to your model:

1. I broke the RANK item out of the Quarters group. The sum(summary(Quarters)) formula was conflicting with the RANK formula causing a circular reference.

2. In the RANK function, you were calling an intersection of a category and an item in the same Quarter dimension. What you really want to do is slice across the Geography dimension and the Average of Quarters item… that is you want to rank the values of Average of Quarters across all the Geography items (states). This results in the RANK formula looking like this:

RANK = rank(Geography:Average of Quarters,Geography:Average of Quarters)

I have made these changes in the attached model. Hope this helps!

-Mike

0

I think there may have been a problem in uploading the model, so here it is again.

Thanks!

Carl

0

Ok, I am trying to do ranking in a difference format, and having some trouble here. In following the formula from another example, I know I am doing something wrong. My feeling is that I need to gain a better understanding of how to specify number and ranges within a particular category. Any in-depth tutorials on this?

Here is the model.

0

Right on! Everyone at Quantrix is very pleased you and your organization is moving forward with Quantrix. Welcome!

-Mike

0

Mike,

Thanks for all your help. I have just convinced my department to get Quantrix, so we will be ordering 3 commercial licenses. Of course, we still need to get through the process with our slow IT department, but my manager has approved. Yeah! I am so happy this happened before my 30 days ran out.

Carl

0

Kind of an odd one. Formulas look OK like you said. However, if I unprotect the sheet in Excel (Tools > Protection > Unprotect Sheet), edit the formula and press ENTER… the #VALUE goes away.

Not sure why it is happening – but looks like you can get the Excel sheet back in line by tickling the formulas. I have logged in our issue database for investigation.

-Mike

0

Mike,

I exported this file into Excel, and I got an error on the rank function. Strange. I took a look at the formula in Excel, and it looked fine to me. Can you please take a look?

Carl

0

No problem Carl – Glad you figured it out!

The summation sign is present when you use the ‘automatic summary item’ option from the menu. (Insert > Summary item > Average).

If you ‘hand-crank’ the formula it will work fine, but you just won’t get the ‘automatic’ summary item symbol.

-Mike

0

Mike,

I feel silly! I figured out why I was getting that element not found error message. Duh. Still, I wonder why my average does not display the summation sign. Anyways, thanks for your help. You are awesome!

Carl

0

Hi Mike,

Thanks for your reply! I tried to use your changes on the OFHEO matrix, but got some errors. When I used the rank formula, I got this error message ‘There is no element named “Item” …. ‘. Also, when the average formula does not have the summation sign in the ‘Average of Dates’ box. Can you please take a look? Tks.

Carl

0

Hey Carl – Thanks for posting:

I made a couple of modifications to the ‘NAR House Pricing Data’ matrix.

1 – I grouped the date items into a group called ‘Months’.

2 – I added an item called ‘Average of Date’ and created this formula:

Average of Date = average(summary(Months))

3 – I created a RANK item, and added the following formula to rank the date averages:

RANK = rank(Item:Average of Date,Item:Average of Date)

4 – I then created a view, swapped the category axis, and sorted the RANK item in ASC order (Tools > Sort Data > Sort Ascending).

And that did the trick! Please review the attached model file and feel free to post again if any questions.

-Mike