+ Reply to Thread
Results 1 to 8 of 8

Is there a way to find out how closely aligned data is?

  1. #1
    Registered User
    Join Date
    02-01-2017
    Location
    london
    MS-Off Ver
    Home and Business 2013
    Posts
    3

    Is there a way to find out how closely aligned data is?

    Hello,

    I am trying to analyse some data to see how closely aligned groups of data are.

    Problem: trying to see to what extent a group of services are closely aligned to each other

    Services: Libraries | Museums | Education | Recreation

    Criteria against which they are measured: Service objectives | User base | Staff

    Analysis: The services are to be rated against the criteria to see to what extent each service is aligned to another service for that criteria, e.g. to what extent are Library services aligned to Museums/Education/Recreation for each criteria (against a scale of 1 to 5).

    Is there a formula that can help me do this?

    Thanks.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Is there a way to find out how closely aligned data is?

    I'm sure there is a formula (or combination of formulas) that can do this. Do you have a worked example you can describe to us (perhaps upload a spreadsheet?)? Any links to standard procedures (outside of Excel) to performing this analysis?

    If you can find some way to help us understand your requirements and algorithms in non-Excel terms, we should be able to help you translate that into Excel. If you are not sure how anyone anywhere performs this kind of analysis, you may need to find a statistician or business analyst to figure out the algorithms used, then bring those algorithms back here and we can help you put them into Excel.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    02-01-2017
    Location
    london
    MS-Off Ver
    Home and Business 2013
    Posts
    3

    Re: Is there a way to find out how closely aligned data is?

    Thanks MrShorty

    I've uploaded a spreadsheet with a short example of the analysis needed.

    I'm basically trying to see what the best grouping of services is for a group of services, to understand which services would best go together.

    I think I'm trying to understand what the best combination is after scoring each combination. In this example, I think there should be 11 different combinations for grouping the services.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Is there a way to find out how closely aligned data is?

    I do not see how you intend to go from the input data included in the spreadsheet to the actual analysis. I can see that AB=BA=5. Will you always be able to say that AB=BA, or will it be possible for the AB score to be different from the BA score? How do you score the higher combinations? Is the ABC score=AB+AC+BC? Perhaps these are the kinds of questions you are trying to ask me, but I don't know the answers to these questions.

    I might ask this question back -- Given the limited data you have put in your sample, what would you conclude? The AB combination is a good combination (has a high score)? The BD combination is good (has a low score)? Is the ABC combination better than the BCD combination and why? How would you determine that the ABC combination is better than the AB or the AC combinations?

    Perhaps it would help to simplify the thought process. What if you only had libraries and museums and were only looking at service objectives -- how would you decide whether it is better to combine them or keep them separate? That might give us the basis for an algorithm that can be expanded.

  5. #5
    Registered User
    Join Date
    02-01-2017
    Location
    london
    MS-Off Ver
    Home and Business 2013
    Posts
    3

    Re: Is there a way to find out how closely aligned data is?

    Hi MrShorty,

    Your queries have prompted me to re-assess how I am going to do the analysis. Thanks!

    In essence, the analysis would have to be manual, but what I would like Excel to do is to highlight all the possible, non-repetitious (order does not matter) combinations that are possible with a number of items (this example has 4 items - A,B,C,D - but another example could have more than 4 items).

    So for example, with the 4 items above (A, B, C, D), the 15 possible combinations that are non-repetitious are (I worked this out manually);
    1. A 2. B 3. C 4. D 5. AB 6. AC 7. AD 8. BC 9. BD 10. CD 11. ABC 12. ABD 13. ACD 14. BCD 15. ABCD

    Is there a formula or macro that could help me identify all the possible combinations above?

    What I would then do in my analysis is rate each of these combinations against the criteria in the worked example, and then the highest rating would signify the best combination.

    Thanks.

  6. #6
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Is there a way to find out how closely aligned data is?

    Dear monsieur, can calculate combinations with "permutations". If yes then total combinations made : 24
    4 by 3 by 2 by 1 = 24 possible combinations.
    There are 5 basic rules for Ideas for Objectives & Goals for Performance.
    >Accuracy
    >Sales
    >Creativity
    >Knowledge
    >Team Work


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  7. #7
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Is there a way to find out how closely aligned data is?

    Dear monsieur, can calculate combinations with "permutations". If yes then total combinations made : 24
    4 by 3 by 2 by 1 = 24 possible combinations.
    There are 5 basic rules for Ideas for Objectives & Goals for Performance.
    >Accuracy
    >Sales
    >Creativity
    >Knowledge
    >Team Work

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Is there a way to find out how closely aligned data is?

    Generating combinations/permutations is a common programming task. Usually around here VBA is suggested using nested For...Next loops. For example, generating 3 "digit" combinations:
    Please Login or Register  to view this content.
    expand from there to get all combinations.
    This is not the kind of programming I do a lot of, so I am probably not the best one to help build a robust routine that generates all combinations, but that is the basic starting idea.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Imported Data not aligned in excel/table format
    By Jourdanbua in forum Excel General
    Replies: 5
    Last Post: 07-30-2013, 10:00 AM
  2. Match two columns of names while keeping data in rows aligned
    By SpaceWheels in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-27-2013, 02:22 PM
  3. Can't seem to split data into digits right-aligned
    By Noobachu in forum Excel General
    Replies: 2
    Last Post: 06-28-2011, 08:10 PM
  4. Probability Question:closely correlated data
    By envstat in forum Excel General
    Replies: 3
    Last Post: 02-08-2010, 05:34 AM
  5. External data ranges: Keeping information aligned
    By sgibson in forum Excel General
    Replies: 0
    Last Post: 04-25-2007, 05:14 PM
  6. [SOLVED] data not aligned in grid when printing
    By projectx633 in forum Excel General
    Replies: 2
    Last Post: 07-12-2006, 04:45 PM
  7. keeping cells aligned when data is updated
    By vicvega in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-03-2006, 09:35 PM
  8. [SOLVED] how differentiate between 3 closely spaced lines in excel graph
    By CookeMJ in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-12-2005, 09:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1