?
Solved

Excel 2010 Combining cell contents for weird effect

Posted on 2012-04-09
4
Medium Priority
?
272 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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