Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel 2010 Combining cell contents for weird effect

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

610 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