Excel 365 help: How to dtermine average of bottom 1% of data?

craptakular · 29 Jul 2017 at 11:40

I am attempting to do some gaming benchmarks, I have a log of Frames Per Second.

I want to determine what my min 1% value is, I assume this would be an average of the values from 0% to 1%?

How would I go about this?

Billy1280 · 29 Jul 2017 at 11:59

Probably not quite what you're looking for, but i'd just manually organise by FPS low to high, and then calculate how many rows 1% would be, then just do =AVERAGE(A1:A100) or whatever range 1% of total is.

craptakular · 29 Jul 2017 at 12:04

Thanks, I've been googling and found this:

=PERCENTILE(A2:A141,0.01)

This gives me the 1 percentile doesn't it?

datalol-jack · 29 Jul 2017 at 12:05

You sort of answered your own question:

Sort in ascending order.
Find the first percentile using a simple method: index = 0.01 * number of items in the list; round to the nearest integer, so 0.33 is 1, for example.
Then your measure of average of choice (a1:index). Obviously you can stick your list in any column you like, A's just the first one.
Done.

It's not surprising there's an Excel formula for that. Just average it via nesting, and presto. Though don't forget to sort still if you are looking for 'min 1% value'.

[FnG]magnolia · 29 Jul 2017 at 12:15

I'm not saying I dislike sexy Excel talk.

* winks

craptakular · 29 Jul 2017 at 12:24

Thanks guys, I did more fiddling and used conditional formatting to fill in a different colour all the bottom 1% values, it confirms the PERCEBTILE Formula is the one I want.

[FnG]magnolia · 29 Jul 2017 at 12:29

[FnG]magnolia said:
I'm not saying I dislike sexy Excel talk.

craptakular said:
Thanks guys, I did more fiddling and used conditional formatting to fill in a different colour all the bottom 1% values, it confirms the PERCEBTILE Formula is the one I want.

That's the hot stuff, oh yeah.

datalol-jack · 29 Jul 2017 at 12:30

Must be a rainy day in Auckland.

NoobCannon · 29 Jul 2017 at 12:35

Excell is like witchcraft tbh.

datalol-jack · 29 Jul 2017 at 13:12

NoobCannon said:
Excell is like witchcraft tbh.

In places not mathematically sound, too, or so I hear.

FloppyPoppy · 29 Jul 2017 at 15:18

craptakular said:
I am attempting to do some gaming benchmarks, I have a log of Frames Per Second.

I want to determine what my min 1% value is, I assume this would be an average of the values from 0% to 1%?

How would I go about this?

Is this for a 1% & 0.1% low reading. If so I'm positive that this is not how it works and is much more complex

james.miller · 24 Aug 2017 at 08:55

datalol-jack said:
In places not mathematically sound, too, or so I hear.

lol. mini-necro but yes, it's a bit fishy in places.

You can trip Excel up with 6 numbers, possibly less. Consider the following:

0.3 + 0.3 + 0.3 - 0.3 - 0.3 - 0.3 equals zero. Except if you are using sumifs, in which case it's almost zero but not quite.

Dumb.

elmarko · 24 Aug 2017 at 09:45

Average of the bottom 1%? - I'd be surprised if the results were statistically significant pending on how many runs.

No point being that specific unless you have a very large data-set to contend with.