Solved

Excel 2010 Combining cell contents for weird effect

Posted on 2012-04-09
4
266 Views
Last Modified: 2012-06-21
To quote the philosopher Harry Callahan, "A man's gotta to know his limitations."  I know I'm over my head here.

   I'm creating a workbook that will allow the user (me) to track activities relevant to a particular goal.  Up to now, I've reported progress based on activity count.  I now want to assign points to distinguish more from less important activities.
 
   The value of the activity is based on three criteria.
The "who" is the recipient of the activity.
The "what" is the activity and
The "how" is the medium for delivering the action (email, phone, in-person, etc.).


   As you can see below, at this moment, I have 14 varieties of "who," 12 varieties of "how," and 31 varieties of what.

   To start with, I'm using a very simple approach to assigning multipliers to each variable.  Thus, the least important of each would be worth 1 point.  I'm hoping to create a way of multiplying the point values for each variable.  The calculated value of an action done for the least important "who," least important "how," and least important "what" would be 1 (1 * 1 * 1 = 1).
   
   For each variable, I have a prioritized, ranked listing of each item (below).  

  Here's where I'm stuck.  First, how can I combine the 5208 possible combinations (14*12*31) in an automated way?  For example, if I combined the most important of each category (see below), I get "Client / Presentation - 10+ / Post Coaching Value Add."  Translated, this means I'd be doing a presentation for 10 or more people as a value added activity for a client.  I'm saying this would be the most valuable thing I could do for my business.

   That would be one of the 5000+ combinations.

  Next, how can I then combine and calculate the appropriate multipliers to establish a point value for each combination?  I'd like to end up with a two-column look-up table where the first column is the different who+what+how's and the second column is the point value.

   Finally, and perhaps primarily, am I crazy?

   In the end, I'd like to be able to enter a "who," "what," "how," and have the point value show up in a column that can be tabulated.  I wonder if this a pivot table opportunity, but I've never tried that feature.

Below are my current variables with values:

Rank      Who      
1      Client      2.3
2      Friend      2.2
3      Referral Source      2.1
4      Team member      2.0
5      Prospect      1.9
6      Former Client      1.8
7      Toastmaster      1.7
8      Contact      1.6
9      Suspect      1.5
10      VIP      1.4
11      Sphere-Medium      1.3
12      Vendor      1.2
13      Sphere-Low      1.1
14      Sphere-Very Low      1.0
            
      How      
1      Presentation - 10+      2.1
2      Presentation - < 10      2.0
3      Face-to-face      1.9
4      Phone      1.8
5      Webinar      1.7
6      Card      1.6
7      Newsletter      1.5
8      Blog      1.4
9      E-mail      1.3
10      Web Site      1.2
11      Text      1.1
12      Internet      1.0
            
      What      
1      Post Coaching Value Add      4.0
2      Post Contact Value Add      3.9
3      Video taped a newsletter / podcast      3.8
4      Wrote a BLOG      3.7
5      Seek referral-engine connection      3.6
6      Host a presentation, etc.      3.5
7      Phone Conversation      3.4
8      Extend invitation to connect with 1+      3.3
9      Extend invitation to connect with 1      3.2
10      Warm connection to someone new      3.1
11      Deliver on a promise      3.0
12      Connect two people I know      2.9
13      Coaching Session      2.8
14      Follow-up action      2.7
15      Meet for Lunch, etc.      2.6
16      Propose a presentation      2.5
17      Pursue presentation opportunity       2.4
18      Responded to an inquiry      2.3
19      Returned phone call / email      2.2
20      Review/update prospect list      2.1
21      Schedule a call or meeting      2.0
22      Linked-In Recommendation      1.9
23      Linked-In Invitation      1.8
24      Linked-In Update      1.7
25      Continuation to schedule      1.6
26      Attend a meeting      1.5
27      Asked for a favor      1.4
28      Called - message      1.3
29      How you doin'?      1.2
30      Toastmaster Business      1.1
31      Called - no message      1.0

Thanks for reading this far and thanks for any help you can give me.
0
Comment
Question by:coachjim
  • 2
4 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 325 total points
ID: 37826144
Here is a file showing how to get the point value of a combination using vlookup
Priority-table.xlsx
0
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 175 total points
ID: 37826179
Hi, coachjim.

You snooze, you lose -  ssaqibh comfortably beat me to it, but I've done this so I'm posting it anyway!

The actual mechanics of dealing with this is pretty straightforward. In the attached file, I've set up separate tables for Who, How and What. I've shown two solutions...
(1) The first is your end-game, as I've understood it. Select the appropriate value from each of the drop-downs. The score is automatically shown for Who, How and What and they're also multiplied together to give the Total.
(2) I provided the second solution in case you have a list of possible How/How/What combinations that you wanted to, say, prioritise. So, in K13:M43, I've entered a number of possible combinations, with their scores automatically calculated in N13:Q43.

Regards,
Brian.Scoring.xlsm
0
 
LVL 2

Author Comment

by:coachjim
ID: 37827058
Thank you both.  I suspect I've got my solution here, but it will take me some time to absorb them and decide which is best...if I can do that.

Thanks for now.
0
 
LVL 2

Author Closing Comment

by:coachjim
ID: 37840680
Thanks a ton for your help.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now