• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Variable Averages based on listing

carver

New Member
Hi Guys,

I am working on this worksheet that would collect a listing of scores given to items.
The list can have multiple occurrences of different items.

Screenshot%202016-02-06%2023.50.03.png


The idea is that all those scores could be gathered in an average value on another sheet.
Here for example, I wish that that blue highlighted box below contains automatically an average to the lower grade (in case the result doesn't exactly match a grade, I don't want halves) of the values in the tables above concerning "Frog Morton on The Town". In this case, I'd like this bow to show "1 - Extremely Mild".

Screenshot%202016-02-06%2023.51.53.png


These values are prelisted in a range for validation purposes.

Screenshot%202016-02-06%2023.54.58.png


Would anyone have an idea?

Thanks a lot.

Carver.
 
Hey Carver,

Can you please share a sample file..it gets easy for members to offer a precise solution..
 
OK

This is the content and explanations about the file:

4 tabs
Bases = contains Named ranges and useful stuff
List = entire list
Have = elements of the list that I possess
Smokes = Scores of the blends that scored through time.

Information comes from the listing in "Smokes" and I want the "My (ratings)" columns in "Have" relating to a specific blend should be updated with the averages of the scores for that specific blend from "Smokes".

I hope I make myself clear. :s

Cheers.
 

Attachments

  • sample.xls
    257.5 KB · Views: 2
On the Have worksheet

First remove the Data Validations on Columns E:H

Then in Cell E2:
=IFERROR(INDEX(SMOKES!C$2:C$327,MATCH($B2&" - "&$A2,SMOKES!$A$2:$A$327,0)),"")
Copy across and down

If your using Excel pre 2007 which I suspect you maybe use:
=IF(ISERROR(INDEX(SMOKES!C$2:C$327,MATCH($B2&" - "&$A2,SMOKES!$A$2:$A$327,0))),"",INDEX(SMOKES!C$2:C$327,MATCH($B2&" - "&$A2,SMOKES!$A$2:$A$327,0)))

see attached file:
 

Attachments

  • Carver_sample.xls
    253 KB · Views: 2
Last edited:
HI hui,

Thanks a lot for your help.
I tried the file you sent.
I am afraid it only does a match, not an average of several scores.

See, here, I added a few records on "smokes" sheet on one blend. But it doesn't calculate the average on "Have".

Smokes:
Screenshot%202016-02-07%2011.18.54.png


Have:
Screenshot%202016-02-07%2011.20.19.png


However, haha, you assume wrong, I don't use Excel prior to 2007, I use 2011 for Mac.
 
Have: E2: =IFERROR(SUMPRODUCT((IFERROR(VALUE(LEFT(SMOKES!C$2:C$327,1)),0))*(SMOKES!$A$2:$A$327=$B2&" - "&$A2))/SUMPRODUCT(--(SMOKES!$A$2:$A$327=$B2&" - "&$A2)),0) Ctrl+Shift+Enter
Copy across and down

Now if you could change the data structure on the smokes worksheet to have simply numbers in columns E:H instead of the combined Numbers and text
E2: could become =IFERROR(AVERAGEIFS(SMOKES!$C$2:$C$327,SMOKES!$A$2:$A$327,$B2&" - "&$A2),0)
 
I just finished a little tweak and added a column next each E:H which would only show the number (1 to 10)

Screenshot%202016-02-07%2012.23.00.png


But the other thing is that the 2 lists are not matching.
It's not one line per line. HAVE has one unique element of each.
SMOKES has several of each or none.

The idea is that in HAVE, E column would be an average score made of the sum of all scores given to an element in HAVE.

If never scored in HAVE, score is 0, if "SMOKES" has 3 evaluations of FrogMOrton on the town, then HAVE would reflect the average scoring made in "SMOKES".
 
So E2: becomes
=SUMPRODUCT((SMOKES!$A$2:$A$327=$B2&" - "&$A2)*SMOKES!D$2:D$327)/SUMPRODUCT(--(SMOKES!$A$2:$A$327=$B2&" - "&$A2))

F2:
=SUMPRODUCT((SMOKES!$A$2:$A$327=$B2&" - "&$A2)*SMOKES!f$2:f$327)/SUMPRODUCT(--(SMOKES!$A$2:$A$327=$B2&" - "&$A2))

G2:
=SUMPRODUCT((SMOKES!$A$2:$A$327=$B2&" - "&$A2)*SMOKES!h$2:h$327)/SUMPRODUCT(--(SMOKES!$A$2:$A$327=$B2&" - "&$A2))

H2:
=SUMPRODUCT((SMOKES!$A$2:$A$327=$B2&" - "&$A2)*SMOKES!j$2:j$327)/SUMPRODUCT(--(SMOKES!$A$2:$A$327=$B2&" - "&$A2))
 
Last edited:
Hi Hui,

Again, thanks a lot for the help and time. I am also working on my side.
I tried your formulas and, weirdly, it's not working.

I was trying to do baby steps by calculating a SUMIF on smokes to start gathering the info but even there, not doing the math.

Screenshot%202016-02-07%2012.47.05.png


The "0" below show the result of the formula above. It seems that it should work but it doesn't. Any idea?
 
So E2: becomes
=SUMPRODUCT((SMOKES!$A$2:$A$327=$B2&" - "&$A2)*SMOKES!D$2:D$327)/SUMPRODUCT(--(SMOKES!$A$2:$A$327=$B2&" - "&$A2))

F2:
=SUMPRODUCT((SMOKES!$A$2:$A$327=$B2&" - "&$A2)*SMOKES!f$2:f$327)/SUMPRODUCT(--(SMOKES!$A$2:$A$327=$B2&" - "&$A2))

G2:
=SUMPRODUCT((SMOKES!$A$2:$A$327=$B2&" - "&$A2)*SMOKES!h$2:h$327)/SUMPRODUCT(--(SMOKES!$A$2:$A$327=$B2&" - "&$A2))

H2:
=SUMPRODUCT((SMOKES!$A$2:$A$327=$B2&" - "&$A2)*SMOKES!j$2:j$327)/SUMPRODUCT(--(SMOKES!$A$2:$A$327=$B2&" - "&$A2))

copy down
 
The 0 is occurring as you have probably extracted the numbers in Column C into Column D as text not numbers
D2: Should be: =VALUE(LEFT(C2,FIND(" ",C2)-1)) etc
 
Hello Hui,

Indeed, I looked that up, made some changes and now it all works well.
thanks again !

Cheers.
 
Back
Top