Average with Values and Frequencies (1 Viewer)

[ArcherSteve]

I have some data that is grouped in two columns, one is "Score Value" and the other is "Frequency" (or the number with that score). Since there are 1000 total scores, is there a way to data statistical analysis (like mean, mode median, etc) on this data without expanding the data to 1000 entries? I've attached the data as a .zip. Any help is appreciated.

 

[HaHoBe]

Hi, ArcherSteve,

what about using Names utilizing Offset and CountA functions to keep the data range variable? Could that help on the topic? It won´t help on the quality or reliability of the data I´m afraid.

Ciao,
Holger

 

[ArcherSteve]

I don't quite understand what you are suggesting. Could you explain it a little more? I'll try to look everything up in my books in the mean time.


Thanks again

 

[HaHoBe]

Hi, ArcherSteve,

if your data is in Column A with a Header in Row 1 the following Formula for the range would deliver a length suiting the number of entries:

Name: ArcherSteve
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)

So you wouldn´t use MEAN(A2:A10) but MEAN(ArcherSteve) instead.

Maybe I got you totally wrong - just my guess at what I read.

Ciao,
Holger

 

[ArcherSteve]

that sounds like it would work! I'll try it asap. Thanks again.

 

[qafself]

Hi,

a crude way to do some of this would be:

create a new column C which is Col A value * Col B value.

cell B13=Sum(b2:B12)
cell C13=Sum(C2:C12)

Mode=Max(B2:B12)
mean=C13/B13

That gets you two of the analysis you want

Ed