This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Hi,

I have a frequency table

Column 1 is the marks and column 2 is the frequency.

Eg

1, 5

2, 5

3, 5

4, 5

I would expect Q1 to be 1.25, Q2 to be 2.5, Q3 to 3.75

Can this be done easily in excel?

I have a frequency table

Column 1 is the marks and column 2 is the frequency.

Eg

1, 5

2, 5

3, 5

4, 5

I would expect Q1 to be 1.25, Q2 to be 2.5, Q3 to 3.75

Can this be done easily in excel?

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Assuming your frequencies are in col. B and start from cell B2 (i.e. B2:B5 as per your sample data)

Try this....

In C2

```
=SUM(B$2:B2)/COUNT(A:A)
```

and copy it down.
I'm not too sure if this is what you want?

but I could be wrong...

28738047.xlsx

for Q1: =$B2*25%

for Q2: =$B2*50%

for Q3: =$B2*75%

for Q4: =$B2

IF it's not related to Column Marks and it's in quartile.

28738047_b.xlsx

What I meant was

1, 5

2, 5

3, 5

4, 5

The frequency table would be expanded to these values

1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4

in column C1:C20

Then using quartile.exc(C1:C20,1) I would get 1.25

quartile.exc(C1:C20,2), I would get 2.5

quartile.exc(C1:C20,3), I would get 3.75

Example 2

What I meant was

6, 5

7, 5

8, 5

9, 5

The frequency table would be expanded to these values

6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 8, 8, 8, 8, 8, 9, 9, 9, 9, 9

in column C1:C20

Then using quartile.exc(C1:C20,1) I would get 6.25

quartile.exc(C1:C20,2), I would get 7.5

quartile.exc(C1:C20,3), I would get 8.75

and eventually, what you intend to do here? I mean, you can use quartile.exc formula to get the results you want, don't you?

28738047_c.xlsm

I have found a way to expand it using excel so we can treat this as closed.

How do I close this?

Can I just delete the question?

Else if you found a solution by yourself, you could post the solution here and then click on the Request Attention button to accept your own comment as the answer.

for more info, check this out:

I answered my own question. How do I close the question?

http://support.experts-exchange.com/customer/en/portal/articles/626549-i-answered-my-own-question-how-do-i-close-the-question-?b_id=44

In column A to H is my original data.

For subject A,

0 students scored 1,

1 student scored 2,

0 student scored 3,

13 students scored 4 .... etc ....

I constructed a cumulative frequency table from column I to column P where

I added one more score 0

I added 1 to the cumulative frequency from column J to P.

I then find the max of column P.

I put 1 in W1.

I filled in with integer values from 1 in W1 to 110 in column EB. (My original data had more rows which I deleted so that the file is smaller .... max of column P was 110.

From W2 to EB2, I use a formula to recreate the raw data file, so that I can find the Q1, Q2, Q3, min and max. You can use other formula to extract what you want from the 'RAW' data.

Accepted answer: 0 points for northward's comment #a41035593

for the following reason:

This was what I tried and worked.

Not very clean but it works.

You may have to adapt it for your own needs.

In column A to H is my original data.

For subject A,

0 students scored 1,

1 student scored 2,

0 student scored 3,

13 students scored 4 .... etc ....

I constructed a cumulative frequency table from column I to column P where

I added one more score 0

I added 1 to the cumulative frequency from column J to P.

I then find the max of column P.

I put 1 in W1.

I filled in with integer values from 1 in W1 to 110 in column EB. (My original data had more rows which I deleted so that the file is smaller .... max of column P was 110.

From W2 to EB2, I use a formula to recreate the raw data file, so that I can find the Q1, Q2, Q3, min and max. You can use other formula to extract what you want from the 'RAW' data.

Freq-table-to-raw.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialNot very clean but it works.

You may have to adapt it for your own needs.

To cancel this request and generate a request for Moderator review, state your reason for objecting in the standard comment box and click the 'Object' button. This question will be closed on 10/16/2015 if there are no objections.

Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.