Solved

Excel 2010 Combining cell contents for weird effect

Posted on 2012-04-09
4
265 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

708 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

15 Experts available now in Live!

Get 1:1 Help Now