EXCEL: Counting Unique Items in Relation to Another Variable

Leave a comment

I tried using Data Subtotal function which it counts the number of occurrences and in the Pivot table the counting/summing tools built into the Pivot Table are not able to distinguish between counting all items or one of each sub-category; it counts all instances of an entry. Bryon Smedley of Tennessee submitted a routine to myOnlineTraininghub to help solve this problem.

In the example below, the Pivot Table would return 8 for BRN-Dante and 15 for BRN-Green Valley. We simply want to know how many permits were issued per BU-Operation.

In other words, 2 permits for BRN – Dante and 6 permits for BRN – Green Valley.

Create a new column in the data and add the following formula next to the first record (cell C2):

=IF(SUMPRODUCT(($A1:$A$24=A1)*($B1:$B$24=B1))>1,0,1)

This will flag the first permit in each permit number group with 1 and all repeated permits with 0 and eliminates you from adding 1 and 0.

Picture1

 

The column can then be added to a  Pivot Table with the SUM function as the operator, counting the number of unique permits per BU-Operation indicating 8 permits in all.

Picture2 

EXCEL: Count Unique Items in a Column of Text

Leave a comment

If you want to determine how many unique entries are in a list. Here we have a list of repeated items but you want to know the number of unique fruits.  There are 9 kinds of fruits: Apple, Banana, Grape, Kiwi, Orange, Peach, Plum, Raisin, and Strawberry.  Unfortunately, there is no single function in Excel that will determine this number. You can use a combination of MATCH and FREQUENT functions nested with IF and SUM to produce the desired result. Bryon Smedley of Tennessee submitted a formula to myOnlineTraininghub.


  1.  Highlight the column of text and name it fruitlist.
  2. Go to an empty cell and type the following formula:
    =SUM(IF(FREQUENCY(MATCH(FruitList,FruitList,0),MATCH(FruitList, FruitList,0))>0,1))
  3. Hit Enter
  1. For an easier formula, highlight the column of text and name it fruitlist.
  2. Got to an empty cell and type the following formula:
    =SUM(1/COUNTIF(FruitList,FruitList))
  3. DO NOT hit ENTER but CRTL+SHIFT+ENTER since it will be an array called fruit list. Excel knows it is a One Table Array. It will also place { } around the formula.


			

WORD: Deleting Text

Leave a comment

Backspace:

One character to the left of the insertion point. Also moves the insertion point back one space.

Delete:

One character to the right of the insertion point, without moving the insertion point.

Ctrl+Delete:

All the characters in the current word, AFTER the insertion point.

Ctrl+Backspace:

All of the characters in the current word, BEFORE the insertion point.

 

POWERPOINT: Function Keys

2 Comments

PowerPoint Function Keys:

Function key Action
[F1] Displays either Microsoft PowerPoint Help or the Office Assistant (depending on your PowerPoint settings).
[F2] Changes the object selected from the text box to the text within the text box, and vice versa. (Only works with text and text boxes.)
[F4] Repeats the last action performed.
[F5] Changes the current view to Slide Show view.
[F6] Moves the mouse insertion point to the next pane.
[F7] Checks your presentation for spelling errors.
[F10] Activates the menu bar. Use the left and right arrow keys to navigate to a menu item; press [Enter] to open an item’s dropdown list; use the up and down arrow keys to navigate to an item on a dropdown list; and press [Enter] to activate a command.
[F12] Launches the Save As dialog box.

Below table lists key combinations for even more convenience. These shortcuts use the Function keys combined with other keys to create quick results.

Key combination Action
[Shift][F1] Launches context-sensitive help. A large question mark appears along with your insertion point when this is activated—click on any menu item or toolbar object for a short explanation.
[Alt][F2] Launches the Save As dialog box.
[Shift][F3] Changes the case of the selected text
[Shift][F4] Repeats last Find (Find Next) operation after the Find dialog box has been closed.
[Ctrl][F4] Closes the active window.
[Alt][F4] Closes PowerPoint.
[Ctrl][F5] Restores the active presentation window size. Use with [Ctrl][F10] to shrink and enlarge the active window within PowerPoint.
[Alt][F5] Restores the application window size Use with [Alt][F10] to shrink and enlarge the PowerPoint application window.
[Shift][F6] Makes the previous pane active. Use with [F6] to navigate through panes.
[Ctrl][F6] Makes the next presentation window active. Use with [Ctrl][Shift][F6] to toggle through open presentations.
[Ctrl][Shift][F6] Makes the previous presentation window active.
[Shift][F10] Displays the selected object’s shortcut menu.
[Ctrl][F10] Maximizes the active presentation window size.
[Alt][F10] Maximizes the application window.
[Ctrl][Shift][F10] Activates the menu bar.
[Alt][F11] Launches the Visual Basic Editor (VBE).
[Ctrl][F12] Launches the Open dialog box.
[Ctrl][Shift][F12] Launches the Print dialog box.

EXCEL: DATEIF formula

Leave a comment

Most people want to calculate the number of months between two dates but it is very difficult to find documentation on the DATEIF function.  Thank goodness I found the information under a site called My Online Training Hub (Mynda, Co-Founder)

DATEDIF syntax

=DATEDIF(date1,date2,interval)

Date1 is the start date

Date2 is the end date

Interval is the type of interval you want to calculate e.g. days, months, years.

DATEDIF Intervals

d = days. Complete days between two dates.

m = months. Complete calendar months between two dates.

y = years. Complete calendar years between two dates.

yd = Complete calendar days between two dates as though the end date is in the same year as the start date.

ym = Complete calendar months between two dates as though the end date is in the same year as the start date.

md = complete calendar days between two dates as though the month and year of the end date is the same as the start date.

DATEDIF examples:

Ideas for Using DATEDIF:

  • Your age in days, months and years.
  • Length of service of an employee.
  • Equipment age.
  • Countdown to a date.

DATEDIF Errors

  1. If Date 1 is later than Date 2 Excel will return a #NUM error.
  2. If Date 1 or Date 2 is not a valid date you will get a #VALUE error.
  3. If the interval is not one of the above options Excel will return a #NUM error.