I’m Unsure How Official This Ranking System Really Is…

Being the 115th ranked general Excel blog on the internet sure has a nice ring to it. Well deserving of your e-mail subscription (located in the bottom right hand corner) if I was to be rather James Blunt about it. But what good is a rank if there are multiple categories to be considered?


“Voted best burger in town by

some guy who likes burgers.”

– Yelp –

DOWNLOAD WORKBOOK


In this lesson we’re going to create cascading drop downs and in cell graphics to rate popular (and less than popular) movie sequels. First things first: let’s create some controls around our rating system.

  • Hover above the column [Rating] until a downward black arrow appears. Then, click to select the table columns data body range.
  • Select the Data tab then Data Validation enter the following information below within the respective tabs.
  • Settings
    • Change Allow: to Whole number and set the following values
      • Minimum Value: 1
      • Maximum Value: 5
  • Input Message
    • Title: Rating System
    • Input message:

5 = “I Loved It!”
4 = “I Enjoyed It!”
3 = “I Feel Indifferent.”
2 = “I Did Not Enjoy It.”
1 = “I Hated It!”

  •  Error Alert
    • Title: Invalid Rating
    • Error Alert: Please enter a rating between 1 and 5.

When you select a cell within the [Rating] column, you will now notice the Input Message prompt next to the selected cell. I prefer to move this to a static position. To do so, click on the yellow prompt and drag it to the top of the worksheet. You will now notice that no matter what area you select within [Rating] it will remain at the top of the screen. (If you were to scroll down, it would continue to remain at the top of the current view.) Take a few minutes to rate the different movies – also try testing your Error Alert by entering a value either less than 1 or greater than 5.


Database Index

I know, I know, I know… databases – ughhhh. No, trust me! This is good. We’re going to borrow from database concepts and create a index with unique auto numbering. It will make more sense as we go but this is easy – you got this!

Function: ROW

Describe It:

  • Returns the current row

Syntax:

  • =ROW([reference])
    • [reference] is optional
    • Arguments in square brackets are always optional. Now, say that ten times fast so you remember.

Note:

  • ROW is a volatile function. Anytime your Excel worksheet is recalculated it will need to determine the position of the cell.

Make It:

  • Field: [ID]
  • =ROW()-1

BAM! Just like that: 1 thru 25. Okay, a bit anticlimactic – I get it. As we extend this concept to other columns, you’ll appreciate this lifesaver.


Let’s get rank out of the way so that we can identify the problem and discuss its downstream impact. Enter the following formula within the [Rank] column.

Function: RANK

Describe It:

  • Determines a values rank within a range either by its ascending or descending position.

Syntax:

  • =RANK(number, ref, [order])
    • Number is the value you want to compare
    • Ref is the array in which all values are stored
    • [order] is optional – defaults to descending
      • 1 for Ascending
      • 0 for Descending

Make It:

  • Field: [Rank]
  • =RANK([@Rating],[Rating],0)

Talk It Out: If there are multiple results with the same value, Excel will assign them the same ranking. For example anything that is rated as a 5 would be assigned the same ranked value of 1, as this is the highest ranked position one can achieve. To overcome this limitation, we will use a helper column and a little bit of creativity.


Refresher: MAX

Make It:

  • Field: [Unique Rating]
  • =[@Rating]+([@ID]/(MAX([ID])+1))

Talk It Out: Since our movie rankings are whole numbers, we want to use a method to add a decimal value between 0 and 1, so that a movie rating of 4 will never reach a 5. This is where the unique ID comes in handy by dividing each ID by the maximum ID plus one. For instance a record of 25 divided by the maximum record of 25 would equal 1, but by adding one digit more you can ensure that it will never achieve a whole number.


Alright, alright, alright…
Each movie now has a unique rating.

Change It:

  • Field: [Rank]
  • =IFERROR(RANK([@[Unique Rating]],[Unique Rating]),””)

It’s All About Position


Now that our movie ratings are all unique, let’s concentrate on removing duplicate years and genres. Things will get a little interesting but stick with me – we’re in that next level stuff now.


Year

Make It:

  • Field: [Unique Year]
  • =IF(COUNTIF($D$2:D2,[@Year])=1,[@Year],””)

Talk It Out: We are going to achieve our goal using an array based on the relative current position. In the formula we use an absolute position of $D$2 mixed with a relative reference of D2. For row 2 this formula simply reads as rows 2:2, but in row 3 you will see your formula has now changed to $D$2:D3. Well that’s interesting – row 4 is $D$2:D4. Hey now! What is this dark magic?! Maybe we can visualize this real quickly in a table and then talk more about it.

Unique Year Array Answer the Question
Row 2 {2007} Is the year 2007 found in the array once?
Row 3 {2007, 1986} Is the year 1986 found in the array once?
Row 4 {2007, 1986, 1989} Is the year 1989 found in the array once?
Row 5 {2007, 1986, 1989, 2004} Is the year 2004 found in the array once?
Row 12 {2007, 1986, 1989, 2004, 1991, 1978, 1990, 1987, 2017, 1984, 1989} Is the year 1989 found in the array once?

The absolute reference of $D$2 remains constant, but the relative reference continues to grow – adding more and more elements to your array the further your range extends. If you look back at the formula and the value is only found in the array once, return the current row’s year. Otherwise, Excel will simply return FALSE if it is found more than once in your array as shown above in the Row 12 example.


Refresher: LARGE

Make It:

  • Field: [Year List]
  • =IFERROR(LARGE([Unique Year],[@ID]),””)

Sure glad you had that ID field to satisfy the k argument. Return 1st largest, return the 2nd largest, return the 3rd largest. Wow. I told you that whole database index thing was going to be worth it!


Genre

Make It:

  • Field: [Genre List]
  • =IFERROR(INDEX([Genre],MATCH(0,COUNTIF($K$1:K1, [Genre]),0)),””)
    • Before pressing Enter to complete your formula, press and hold Ctrl+Shift+Enter to force an array calculation
    • When complete, your formula should include the following curly brackets { } denoting that this is an array formula.

We haven’t talked about CSE calculations yet. If you’re anything like me, you’ll find array calculations to be both incredibly fascinating and frustrating at the same time, but we’ll hit on that topic in a later lesson.

Talk It Out: Return the index position from the where the match value is equal to 0 ( TRUE = 1, FALSE = 0)

Genre List Array Return Position
Row 2 {0;0;0;0;0;0;0;…} First Index Position Equals Zero
Row 3 {1;0;0;0;0;1;0;…} Second Index Position Equals Zero
Row 4 {1;1;0;0;0;1;0;…} Third Index Position Equals Zero
Row 5 {1;1;1;0;1;1;0;…} Fourth Index Position Equals Zero
Row 6 {1;1;1;1;1;1;0;…} Seventh Index Position Equals Zero

When you get to row six, you’ll notice that Adventure and Horror have now shown up twice, and the first unique value is now found at index position 7. The [ID] field you created earlier is a nice guide to follow along with to determine each element’s position in the array.


Whew… now that we got that out of the way let’s go ahead and start our lesson. “WHAT?! ARE YOU KIDDING ME?!” you say. I know… this was a lot of prep work, but you’re in the business of building workbooks that are meant to last. The last thing you want for your career is something that always breaks that your name will be synonymous with until it’s caput. Let’s move on to creating a dependent drop down, some boolean logic and then we’ll bring it on home with some pretty stuff – a.k.a. the stuff others actually care about.


Drop Downs

  • Select the Formulas tab then Name Manager
  • Select New…

Year

  • Name: yearDropDown
  • Scope: Workbook
  • Refers to: =Data!$J$2:INDEX(MovieRatings[Year List],COUNTIF(MovieRatings[Year List],”>0″))

Genre

  • Name: genreDropDown
  • Scope: Workbook
  • Refers to: =Data!$K$2:INDEX(MovieRatings[Genre List],COUNTIF(MovieRatings[Genre List],”?*”))

Real Quick: We know that “>0” means, “Count all values in a range that are greater than zero,” but what in the world is this “?*”. The question mark means, “To find any single text character while in combination with an asterisk,” and allows it to find one or more text characters.

Try This:

  • =COUNTIF(MovieRatings[Year],”?*”)
    • Returns a value of zero because the year column contains numbers.
  • =COUNTIF(MovieRatings[Genre],”H*”)
    • Returns a count of genres that start with the letter H. Not case sensitive.

Make It:

  • Select the Dashboard worksheet
  • Select cell F4 and go to the Data tab and then Data Validation
    • Allow: List
    • Source: =genreDropDown
  • Select cell F5 and go to the Data tab and then Data Validation
    • Allow: List
    • Source: =yearDropDown

If you click on cells F4 and F5 you won’t notice anything special – they return the values of the respective [Year List] and [Genre List] fields from the data tab but they fail to interact with one another. You know what that means?! More prep work!


Back To Work:

Select the following cells and go to the Name Box to type in the variable name

  • F4: genreChoice
  • F5: yearChoice
    • Stored variables are now listed in the Formulas tab Name Manager.


Function: AND

Describe It:

  • Checks whether all arguments result in a TRUE

Syntax:

  • =AND(logical1,[logical2],…)

Function: OR

Describe It:

  • Checks whether any of the conditions result in a TRUE

Syntax:

  • =OR(logical1,[logical2],…)

Make It:

  • Return to the Data worksheet
  • In the [Include] field, add the following formula:
    • =AND(OR([@Genre]=genreChoice,genreChoice=””),OR([@Year]=yearChoice,yearChoice=””))

Change It:

  • Field: [Unique Rating]
  • =IF([@Include],[@Rating]+([@ID]/(MAX([ID])+1)),””)
    • If [Include] is TRUE, then calculate the random rating, else return blank
  • Field: [Unique Year]
  • =IF((COUNTIF($D$2:D2,[@Year])=1)*[@Include],[@Year],””)
    • Boolean: TRUE = 1, FALSE = 0

 

Talk It Out: Basic multiplication tell us any number times zero (FALSE) returns zero, and any number times one (TRUE) returns the number. Adapting this process in conjunction with PEMDAS, you can break your problems into smaller chunks – starting with the innermost parentheses:

  • (The count of 2017 = 1) * TRUE
    • TRUE * TRUE = TRUE
    • 1 * 1 = 1
  • (The count of 2017 = 1) * FALSE
    • TRUE * FALSE = FALSE
    • 1 * 0 = 0

It’s NOT(Not About The Cell)


We want to make our lists multi-directional. If you select the year first, you only see the genres that are applicable and vice versa. For this we want to utilize our [Include] column, as well as some creative thinking, to get to our desired result.

Function: NOT

Describe It:

  • Returns the inverse of a logical expression
    • If TRUE return FALSE
    • If FALSE return TRUE

Syntax:

  • =NOT(logical)

Change It:

  • Field: [Genre List]
  • =IFERROR(INDEX([Genre],MATCH(0,(COUNTIF($K$1:K1, [Genre])+NOT([Include])),0)),””)
    • Hold Ctrl+Shift+Enter to force an array calculation when completing your formula

 

Talk It Out: Our match criteria is 0 to ensure we are capturing unique genres only. Unfortunately for our [Include] column, if the result is FALSE, we remember this result will be a 0. Being the incredibly resourceful, creative, and extremely overqualified person that you are – I bet you were thinking, “Well earlier in this blog you said TRUE = 1. So just use the opposite of FALSE and be done with this already.” I couldn’t agree more! WE’RE DONE!… With the data at least. Let’s do some pretty stuff on the Dashboard tab and we’re home free.


Five Star SQL’s

  • Go to the Insert tab
  • Select Symbol
  • Change the font to Wingdings and locate the filled star and hollow star symbols. Make note of their Character Code below.
    • Solid Star: Character code: 171
    • Star Outline: Character code: 182
      • If you want to insert into spreadsheet either double click the symbol or select insert.

Symbol Menu


I went ahead and did a lot of the heavy lifting with the INDEX/MATCH on the Dashboard tab since I know you are already a LOOKUP master. All I want from you right now is to go ahead and wrap up that pretty picture with some neat tricks. After this monster of a lesson, you deserve it.


Function: REPT

Describe It:

  • Repeats text a given number of times
    • Must be a positive, whole number

Syntax:

  • =REPT(text,number_times)

Make It:

  • Field: [Rating]
  • Change Font: Wingdings
  • =IF([@[Rating-Lookup]]<>””,REPT(CHAR(171),[@[Rating-Lookup]])&REPT(CHAR(182),5-[@[Rating-Lookup]]),””)

Talk It Out: As long as the [Rating-Lookup] column is not blank we will utilize the REPT function to first return solid stars. We will concatenate our solid stars with our star outlines by utilizing the ampersand character (&) to join the strings of text together. Knowing that our maximum scale is 5, we want to return 5 minus the movie rating. If the movie rating is 5 – great! It repeats the star outline character 0 times. If the movie rating is three, though, you will repeat 3 solid star characters and 2 star outlines ( 5 – 3 = 2).


Clean It Up:

Worksheet: Dashboard

  • Hide columns A & B

Worksheet: Data

  • Select columns F thru K
    • Using the fill color, select a light grey to fill in all cells.
    • Using the font color, select Automatic
      • The grey fill is my calling card for workbooks that I’ve developed. I utilize this method to remind myself these are helper columns that contain formulas. The remaining cells in the table contain unaltered data. When you collaborate with others, it’s good to share your methods or thought process so that they can easily navigate your work or edit as needed for their own purposes.
    • Hide columns

Wrap It Up: Oftentimes, the pretty picture is all that people care about and that is 100% okay. For Excel developers like me and you, though, it’s about more than just the finished product. I’ve said it before and I’ll say it again – the success of your workbook is not based upon pretty charts or intricate formulas, but in how it stands the test of time (or Office Update). Your workbook was built to last. Great job.


“Excel lets things look professional, and people assume there’s substance behind it.”
Douglas Klunder

You’re still here? It’s over. Go home. Go.

4 thoughts on “I’m Unsure How Official This Ranking System Really Is…

  1. If you want to build an ever increasing client base that returns again and again, than this is what you need to do, taken from above:

    “but in how it stands the test of time (or Office Update). Your workbook was built to last. Great job.”

  2. Pretty much my notes from reading all your posts, keep writing mate:

    “Great Formulas Last Forever.” Excel is not about doing more – it’s about doing more with less and finding consistencies that exist not only in your data but in the real world.

    Great workbooks stand the test of time. This is a fact. The more you continue to work in or around data, the more you will find that data dimensions follow similar patterns, generally revolving around names, dates, times and counts. Consistently finding inventive ways to show this information is what sets developers like yourself apart.

    Recognizing what data is and understanding what data could be is what separates you from the rest of the crew. This is your Mad Maximum Value.

    Simply knowing what exists will improve your long term development. The rest will come in time as you continue use.

    Data is full of possibilities, always ask the questions first –
    Who? What? When? Where? Why?

    Constantly developing your cognitive skills in relation to logic, reasoning, memory and processing can undoubtedly lead to more meaningful data analysis on your part.

    “If you’re going through hell, keep going.”
    Winston Churchill

    That feeling of being somewhat overwhelmed is called being on the verge of a breakthrough.

    I’ll say it for the thousandth time, you will never learn more about Excel than on the day you inherit a broken workbook.

    “The object of art is not to reproduce reality, but to create a reality of the same intensity.”
    Alberto Giacometti

    People care about the end product, and you’re the expert that is going to get them there, no matter how big or small the task is. Excel is a tool, you are the resource. Never lose sight of that.

    People care about the end product, and you’re the expert that is going to get them there, no matter how big or small the task is. Excel is a tool, you are the resource. Never lose sight of that.

    The data is in the real world. Become efficient in Excel, provide solutions and get back out there. If you’re in the trenches and you consistently find yourself with Excel open on your desktop – step back and ask yourself:
    “HOW CAN I USE EXCEL TO SPEND LESS TIME IN EXCEL?“

    From managing the household budget to your kickball team schedule – 99% of what you need to accomplish in Excel can be completed following a simple framework. It’s controversial even saying this, but I don’t believe any Excel workbook should contain more than 3 tabs.

    Data
    Calculation
    Visualization

    Presenting validated information clearly and confidently is more important than that pretty bar chart. Remember that you have moved mountains of data and conquered spreadsheets that others dared not touch. You are the Ayatollah of Spreadsheet-Rolla. But, keep that ego in check as nobody wants to work with someone who makes a situation difficult or tries to make them feel technologically inferior. Find ways to use Excel to elevate those around you to make them better. Remember:

    Excel is your platform, not your purpose.

    Information is not an argument, it’s a conversation.  As a developer, having the ability to remove personal feelings about what you think a workbook should be will take you far in you career.  Always be mindful that your end users may simply want the freedom to drive their own story in an easy-to-use format. And that’s perfectly fine. Not everyone is going to dedicate the time that is needed to be an Excel expert like you. But when they start asking questions about inconsistencies within the data, speak softly and carry a big data validation stick. You’re going to need it

    ut my mission isn’t to make you good at Excel; it’s to make you great at it. As an Excel developer, being as efficient and effective as possible in delivering interactive solutions to your end users will make you streets ahead.

    There is no such thing as “the right time” when it comes to developing your talents in Excel. You are always on call as the swiss-army-knife of information. Today a financial analyst, tomorrow a visualization specialist… within each role you are consistently relied upon to evolve your talents at a moment’s notice. So, throw away your abacus because you’re about to learn one of the best tools for your arsenal.

    There are certain moments in working with Excel where your major breakthroughs happen not because something was challenging but because it was so incredibly simple all this time. The PivotTable is EXACTLY that: an easy-to-start tool that is also incredibly powerful for seasoned developers. Don’t wait to start trying out some of Excel’s most robust features (Power Pivot, Power Query, Power View), because in the age of big data the time is NOW for you to make your presence known and become the office professional you were meant to be….
    The Excel Girl | The Excel Guy

    WORK SMARTER,
    NOT HARDER

    it’s a dedication to the 50% of the program that you didn’t even know existed and might never use. But you’ve seen it. You now know it’s POSSIBLE. And knowing it’s possible will help you bring more imagination to your workbooks.

    Creating an efficiency by using fewer characters, easy-to-read formulas and nested functions all sounds great to me, but at the end of the day do it in a way that makes sense for you.

    More data means more analysis, which means more reports.

    If you don’t respect your time no one else will.

    You are the expert. You are the one that everyone comes to for help. You get people out of the last minute jams. People can respect your skills, but if they don’t respect your time you will never truly get anything of value accomplished. Always set time aside for yourself. Use a Tomato Timer to break up the daily monotony. Don’t be afraid to step away from a project to gain clarity. We only have a finite amount of time on this planet. Do not spend each passing second entertaining the distractions.

    Definitely agree with taking a break from a project on a regular basis. Sometimes it feels like the wrong thing to do but that bit of space can help you come back with fresh eyes (and a fresh mind!) to solve the problem you’re looking at. Works for me all of the time!

    I think being conscious of when you’ve gone too far down the rabbit hole is a great skill to have. My greatest place for clarity seems to be when I’m driving in my vehicle. Something about the windows down, music up and no immediate access to a workstation in those moments something just *CLICKS* and a solution comes charging out of nowhere like a freight train.

    “What gets measured gets managed.”
    Peter Drucker

    Analysts estimate anywhere from 50-80% of their time is dedicated to cleansing information (Source: New York Times).

    Ultimately, though if the majority of your time is spent cleansing unstructured information, you have two choices: either start loving it or start listing it.

    look for every creative opportunity possible to market yourself and your talents to make it to the top of the list.

    The opportunity to work with data shouldn’t be viewed as a prison cell of dates and numbers, but an opportunity for change. A change in the way leaders conduct business or families balance budgets. No matter how big or small, it is through your black tinted glasses that you see the data for what it truly is – a problem waiting to be solved.

    Front-End Development
    Front-end development is the production of an interface that enables a user to see and interact with objects. As a front-end developer, your design solutions are intended to help users solve problems in a format that is easy to navigate.
    Think of:
    * Charts
    * PivotTables
    * User Controls (Active X, Form, Slicers, etc.)

    Back-End Development
    Back-end development is the production of a database that enables users to query information and return results in the form of a dynamic front-end application. As a back-end developer, you will design solutions to meet the data modeling requirements necessary for the front-end application. Data modeling ultimately should serve to manage information and establish a standardized process for analysis.
    * Table Objects
    * Using Functions for Helper Columns
    * Structured Query Language (SQL)
    * PowerQuery

    Think Front-End Development If You:
    * Want to share a workbook with others to easily assist in telling the story of their data
    Think Back-End Development If You:
    * Want to normalize and structure data to be controlled by an object

    determining the purpose of each component in your project can greatly increase the longevity of your workbook, along with its distribution and future enhancements.

    Data is and always will be your number one priority and it is your job to protect it. The corruption and or bloat of workbooks is a very real thing, and sometimes cannot be recovered within the application. You should aim to NEVER build yourself into a corner that puts you at this much risk. Following a similar approach of Model-View-Controller (MVC) architecture you should seek to segment your workbook into three layers within Excel:
    * DATA: The management and structuring of raw data
    * VISUALIZATION: The representation of data through visual design objects
    * CALCULATION: The representation of data through functions or aggregate objects

    I still stand by my statement that “I don’t believe any Excel workbook should contain more than 3 tabs.” and it is because of the many benefits you can reap using a Data-Visualization-Calculation (DVC) design architecture.

    “In the beginner’s mind there are many possibilities, but in the expert’s there are few”
    Shunryu Suzuki

    Advanced users of Excel are not people who rate themselves an “8 out of 10” – they are people with the courage to admit that which they do not know and the humility in knowing that there is always much more to learn. Take this from a solid “4 out of 10.”

    The founding fathers of Multiplan intended that spreadsheets would be for everyone: the rich and the poor, the data analyst and the data scientist, the sighted and the vision impaired. Often, things that we take for granted make a world of difference to others.

    The loss of sight does not impair the qualities of mind and heart.”
    Helen Keller

    Franklin Delano Roosevelt served four terms as President Of The United States of America. Claude Monet painted his famous Les Nymphéas. Stevie Wonder shaped modern music with over 25 Grammy awards. All three dealt with varying degrees of vision impairment. Perspective is not limited to your visual perception, it’s in the way you experience information and the stories you are able to share. There’s no excuse for your spreadsheets to be any different.

    All this time you’ve been writing code and you didn’t even know it. Telling yourself you are too far into your career, don’t have enough time, or that programming is just too intimidating – are all now thrown out the window. You’ve done it. You are doing it. You can continue to do it.

    Data should always be viewed objectively.

    If repetition is the key to mastery, repeating an activity the wrong way is a killer in disguise

    Data entry and manipulation is a necessary evil. FACT. I told you the distance between using Excel and mastering Excel is not long.

    “An amateur practices until he can do a thing right, a professional until he can’t do it wrong.”
    Percy C. Buck

    Being good at something is the bare minimum required for entry.  There is no shame in desiring to be the absolute best and vocalizing this desire among friends, family or colleagues. Find a mentor, read some books, embrace a year of fear – whatever needs to be done to get you to your goal I say fake it until you make it. People want to invest in people who invest in themselves.

    Oftentimes, the pretty picture is all that people care about and that is 100% okay. For Excel developers like me and you, though, it’s about more than just the finished product. I’ve said it before and I’ll say it again – the success of your workbook is not based upon pretty charts or intricate formulas, but in how it stands the test of time (or Office Update). Your workbook was built to last. Great job.

    “Excel lets things look professional, and people assume there’s substance behind it.”
    Douglas Klunder

    1. Appreciate it immensely Nick! It’s a labor of love and torture at times with all the changes that Microsoft has put in lately to make certain workarounds a thing of the past. The future is bright for those of us who enjoy the challenge.

Questioning My Mental Health?