Help!!!!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Krish - 11 Oct 2007 17:23 GMT

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!!!!!!!!!!

Reply to this Message

Sandy Mann - 11 Oct 2007 19:40 GMT

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.

Signature

HTH

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Step 1: I have range of numbers lets say 50, 25, 30 and so on....
>
[quoted text clipped - 22 lines]
>
> Please help!!!!!!!!!!

Reply to this Message

Bernard Liengme - 11 Oct 2007 20:29 GMT

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

Signature

Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

> Try:
>
[quoted text clipped - 32 lines]
>>
>> Please help!!!!!!!!!!

Reply to this Message

Sandy Mann - 11 Oct 2007 21:00 GMT

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:

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

to mean, (with your range), 10, 9.9, 9.9, 9.9, 9.9

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

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:

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

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?

Signature

Regards,

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> 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
[quoted text clipped - 39 lines]
>>>
>>> Please help!!!!!!!!!!

Reply to this Message

Sandy Mann - 12 Oct 2007 00:32 GMT

Hi Again Bernard,

> So it does not weight for the duplication.

Why should it? The OP said:

>>> 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;

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))

Signature

Regards,

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> 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
[quoted text clipped - 39 lines]
>>>
>>> Please help!!!!!!!!!!

Reply to this Message

Krish - 12 Oct 2007 02:54 GMT

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

> Hi Again Bernard,
>
[quoted text clipped - 90 lines]
>
> - Show quoted text -

Reply to this Message

Bernard Liengme - 12 Oct 2007 13:52 GMT

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

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> Hi Again Bernard,
>
[quoted text clipped - 63 lines]
>>>>
>>>> Please help!!!!!!!!!!

Reply to this Message

Bernard Liengme - 11 Oct 2007 20:17 GMT

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

Signature

Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

> Step 1: I have range of numbers lets say 50, 25, 30 and so on....
>
[quoted text clipped - 22 lines]
>
> Please help!!!!!!!!!!

Reply to this Message

Rate this thread: