Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2014
    Location
    Perth, Australia
    Posts
    2
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Must be a formula (array?) that can do this - but I can't figure it out

    Hello all,

    I have a spreadsheet, attached, where I have a grid of data.

    Each column represents a person, and each person has listed their favourite foods.

    So in the first column, I have:

    Alice
    Curry
    Lasagne
    Pizza
    Toad in the Hole
    Fajitas
    Sandwich

    and in the second column,

    Bob
    Pizza
    Toad in the Hole
    Cottage Pie
    Sandwich
    Lasagne
    Gammon and Egg
    Chocolate Cake

    Carol
    Shepherd's Pie
    Pizza
    Curry

    and so on.

    There are a number of different foods, and each one might be chosen by Alice, Bob, or one of the other people in the other columns.

    I can easily count up how many times, overall, they vote for Curry - for Alice, Bob and Carol, above, two of them had Curry listed somewhere, so the answer is 2.

    But what I want is to add a weighting to these. So the top answer on each person's list is worth 6, the second one worth 5, then 4, 3, 2 and anything after that worth 1.

    I then want to create a 'score' for Curry. It would be:
    Alice - voted it #1 - 6 points
    Bob - didn't vote for it at all - 0 points
    Carol - voted it #3 - 4 points

    Overall score for Curry: 10 points.


    And then I want to work out the same for Pizza and all the other foods.


    I sort of figure I ought to be able to do this all in one cell, using some sort of array formula. So that I have:

    Food | Score
    Curry | 10
    Pizza | 15
    etc

    I just can't figure out how to do it, other than by using a really annoying long one that does a separate calculation for each row, and adds them all together.

    I have attached the file; my bad formula is in column A in the yellow cells, and does get the right answer... but does anyone know how to rewrite it neatly and concisely so that if I had 800 rows instead of 8, it would still cope?
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    psymann,

    An easy way to do this is with a user defined function (UDF). Enter the following code in a standard module then in cell A2 enter the following formula and copy down to A23:

    =ScoreSum(C2)

    HTH,
    Maud

    foodchart1.png

    Code:
    Public Function ScoreSum(food As String) As Integer
    '-----------------------------------
    'DECLARE AND SET VARIABLES
    Dim rng As Range
    Dim cell As Range
    Dim weighting As Integer
    weighting = 0
    '-----------------------------------
    'MATCH FOOD WITH EACH ROW IN FOOD CHART (D25:P32)
    If food = "" Then Exit Function
        For I = 25 To 32
            Set rng = Range("D" & I & ":P" & I)
    '-----------------------------------
    'MATCH FOOD WITH EACH ROW IN FOOD CHART (D25:P32) AND INCREMENT SCORE
            For Each cell In rng
                If cell = food Then weighting = weighting + Cells(I, 3)
            Next cell
        Next I
    '-----------------------------------
    'RETURN SCORE
    ScoreSum = weighting
    End Function
    Attached Files Attached Files

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    psymann (2014-12-09)

  4. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    In A2:
    =IF(C2="",0,SUMPRODUCT(($D$25:$P$32=C2)*$C$25:$C$3 2))
    and copy down.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. The Following User Says Thank You to rory For This Useful Post:

    psymann (2014-12-09)

  6. #4
    New Lounger
    Join Date
    Dec 2014
    Location
    Perth, Australia
    Posts
    2
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Rory - that was just what I was after - I'd tried a few things that were tantalisingly close, but I'd not really got the ()*() syntax right inside the SUMPRODUCT formula, and had tried to do it with a comma instead of an asterisk, thinking the formula would do the product bit itself.

    Maudibe - I'd not even contemplated a UDF as I'd rather forgotten they were possible. And that will almost certainly help with some other upcoming problems to solve too, so also useful :-)

    Thanks both :-)

Posting Permissions

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