[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel 2010 Combining cell contents for weird effect

Posted on 2012-04-09
4
Medium Priority
?
275 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 1300 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 700 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

829 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