Help!!!!

[Krish]

Step 1: I have range of numbers lets say 50, 25, 30 and so on....

Step 2: I want to find 10% of of the range in above ex 5, 2.5, 3...


Step 3: The numbers to be rounded of to 5,3,3.......


The following will help for first 3 steps i.
=Round(CountA("Range")*10%),0)


Step 4: Now the tricky situation


a) Specific to the range as in above ex for range with 50 numbers the
result shoulbe the Average of the highest 5 numbers, Lowest 5
numbers, Average of the range;


b) for range with 25 numbers the result shoulbe the Average of the
highest 3 numbers, Lowest 3 numbers, Average of the range


c) for range with 30 numbers the result shoulbe the Average of the
highest 3 numbers, Lowest 3 numbers, Average of the range


I have around 61 different ranges in this fashion in different
columns
(no named ranges)


Please help!!!!!!!!!!

 

[Sandy Mann]

Try:

=AVERAGE(ROUND(LARGE(J1:J50,{1,2,3,4,5})/10,0))

and:

=AVERAGE(ROUND(SMALL(J1:J50,{1,2,3,4,5})/10,0))

Adjust as required for other ranges etc.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Bernard Liengme]

Not too clear what you want.
Your example 50,25,30 is meant to be the number of cells in the range NOT
the actual numbers. Correct?
=Round(CountA("Range")*10%),0)
If there are 50 numbers then you get (Round(50*10%,0) = 5
Now you want the average of the top 5; and the average of the bottom 5?

This will sum the numbers that are in the top 5
=SUMPRODUCT(--(MyData>=LARGE(MyData,5)),MyData)
But slow down, we cannot just divide by 5
Suppose the range of numbers are in the interval 1 to 100, and we have 100
once and 99 five times
The top 5 are 100 and 99
This tells you how many numbers are int top 5:
=COUNTIF(MyData,">="&LARGE(MyData,5))
One divided by the oter will give the average (99.17 in the example of five
99s and one 100)
Or in one formula
SUMPRODUCT(--(MyData>=LARGE(MyData,ROUND(COUNT(MyData)*10%,0)))*MyData)/COUNTIF(MyData,">="&LARGE(MyData,5))

For the average of the lowest ones use SMALL for LArge and <= for >=

best wsihes

 

[Bernard Liengme]

Sandy, you neat formula works if I take out /10
But with one 100 and five 99s, it uses only 100,99,99,99,99 giving 99.20
against my 99.16
So it does not weight for the duplication.
I wonder which answer Jerry Lewis (our resident statistical) would plum for.
best wishes

 

[Sandy Mann]

Hi Bernard,

I wonder which answer Jerry Lewis (our resident statistical)

I get lost in stastics very easily. It may be my msunderstanding of what
the OP wants but I read:
to mean, (with your range), 10, 9.9, 9.9, 9.9, 9.9

so they become 10, 10, 10, 10, 10

with an average of course of 10.

Ah! Light is dawning! Are you reading it the the OP is taking 10% of the
range of the numbers? It all makes sense now, 50 numbers - 5 highest & 5
lowest, 25 or 30 numbers, 3 Highest etc.

But that would make:
the average of your range: 100, 99, 99, 99, 99 which is 99.2

If I read your formula correctly, if there were six or more numbers bigger
then or equal to the 5th largest number, ie 100, 99, 99, 99, 99, 99 then all
six numbers will be included in the Average() giving your 99.1666666667
return.

Or am I wrong?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Hi Again Bernard,

So it does not weight for the duplication.

Why should it? The OP said:

Surely the OP wants the average of just 5 numbers not the average of a set
of figures the quantity of which depends on their values. But then as I
said stastistics are not my strong suit.

To give the OP a single formula to account for all his three requirements I
would amend my formula to:

=IF(COUNT(J1:J50)>30,AVERAGE(ROUND(LARGE(J1:J50,{1,2,3,4,5}),0)),IF(COUNT(J1:J50)>0,AVERAGE(ROUND(LARGE(J1:J50,{1,2,3}),0)),""))

With, as you said, replacing the LARGE with SMALL and of course the average
for the range as:

=IF(COUNT(J1:J50)=0,"",AVERAGE(J1:J50))

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Krish]

Hi guys Thanks a lot...... First thing in the morning, I am looking
into these answers.


I will digest the same but initially i see the that for every cell i
should give the number of highest or lowest to be picked. that is 5 in
case of range of 50 members and 3 incase of 25 numbers 4 in case of 35
numbers , so the sample size keeps changing as per the range based on
the size of the range.

Second please dont worry about duplicates in the range

for ex in range of 18 numbers the sample to be taken is 2

10,10,12,25,62,11,24,36,80,90...

here the require answer is
total numbers in the range --? 18 hence sample 18*10%, round (1.8) = 2
average(Smallest, sample size) = Average (10+10) or (10+10)/2

average(Largest, sample size) = Average (80+90) or (80+90)/2 = 85


Regards and Many Many Thanks for your help !!!!!!!!

if the sample size increases according to the range

 

[Bernard Liengme]

If we were asked for the average of the 5 largest values in
2, 3, 5, 20, 99,99,99,99,99,100
we could reply
i) 3, 5, 20,99, 100 are the five largest (unique) numbers
or
ii) we could say, I want the largest 5 numbers, so they are 99,99,99,99,100
which is what your formula does
or
iii) we might argue that because 99 occurs 5 times we should use
99,99,99,99,99 and 100 as I did
I am NOT saying I am right just that the question could be read in more than
one way
Cheers from New Scotland