Dave Bruns
Lookup latest price
The LOOKUP function assumes data is sorted, and always does an approximate match. If the lookup value is greater than all values in the lookup array, default behavior is to "fall back" to the previous value. This formula exploits this behavior by creating an array that contains only 1s and...Read more
First in last out times
In this problem, the goal is to find the first (earliest) time in and the last (latest) time out for a given name. This is essentially a lookup problem and the solution shown in the worksheet is an example of how you can sometimes use minimum and maximum functions to perform lookups. This works...Read more
Highlight unprotected cells
The CELL function can provide a wide range of information about cell properties. One property is called "protect" and indicates whether a cell is unlocked or locked. All cells start out "locked" in a new Excel workbook, but this setting has no effect until a worksheet is protected.
The...Read more
PERMUT Function
The PERMUT function returns the number of permutations for a given number of items. A permutation is a combination where order matters. In other words, a permutation is an ordered combination.
There are two types of permutations:
- Permutations where repetition is not ...Read more
COMBIN Function
The COMBIN function returns the number of combinations for a given number of items. A combination is a group of items where order does not matter. The COMBIN function does not allow repetitions. To count combinations that allow repetitions, use the...Read more
Product mix over time
This is an example of a 100% stacked area chart. This tricky chart type can be quite hard to read. The idea is to visually show a percentage distribution across categories at specific intervals, but you lose the ability to compare absolute numbers, unless you add as data labels. In this case, data labels are only used to display the series name.
Count birthdays by month
You would think you could use the COUNTIF function to count birthdays, but the trouble is COUNTIF only works with ranges, and won't let you use something like MONTH to extract just the month number from...Read more
Popular college degrees for women
I was looking at an article on forbes.com about popular college degrees for women and men. They listed 9 degrees for both women and men (on many, many pages), and this chart is just a mash-up of the data they cited, sorted to show popular degrees for women at the top.
The chart type is 100% stacked bar, and its a good example of how a bar charts can accommodate extremely long labels. The data used in the chart looks like this: