Excel Array Formula Series #5: SUM 3 Largest Values

Hello everyone,

This is a sample of the worksheet that I have:

Code:

A			B
Name			Points
Antonio Margarito	700
Bernard Hopkins		950
Celestino Caballero	450
Israel Vazquez		350
Joe Calzaghe		950
Juan Marquez		800
Kelly Pavlik		500
Manny Pacquiao		1000
Ricky Hatton		600
Vic Darchinyan		800

Names sorted in alphabetical order.

What I need is to choose top 10 from column B and find corresponding them names. These are two array formulas that I use in another worksheet:

=LARGE(B2:B11,ROW(INDIRECT("1:10")))
=INDEX(A2:B11,MATCH(LARGE(B2:B11,ROW(INDIRECT("1:10"))),B2:B11,0),1)

(To simplify, I removed references to another worksheet).

This is what I get:

Code:

A			B
		P4P
Manny Pacquiao		1000
Bernard Hopkins		950
Bernard Hopkins		950
Juan Marquez		800
Juan Marquez		800
Antonio Margarito	700
Ricky Hatton		600
Kelly Pavlik		500
Celestino Caballero	450
Israel Vazquez		350

As you can see, the formula finds only the first match in array and returns it several time. How can I change the formula to return the next occurrence (Joe Calzaghe in this sample)?

Given a table with years across the top axis and month along the side axis with a value for each month. Like this ...

Mon 2007 2006 2005
-------------------------
Jan 14 23 18
Feb 9 17 24
Mar 13 4 12
Apr 28 7 21
May 15 13 22
Jun 14 23 14
Jul 5 14 20
Aug 12 18 17
Sep 8 21 23
Oct 13 25 17
Nov 14 12 22
Dec 22 16 5

The goal is to find the largest monthly increase in values. In the above example, it is 18 (Dec '05 to Jan '06).

This could be done by creating a second table with the differences and find the max in that table or it could be done with a VBA macro. The question is whether it is possible to do so with a formula and what that formula would be. That way the largest increase would be recalculated when the next monthly value was entered.

Ideas ???

Thanks, BruceR

hi,
I am looking to create a formula to add together the two highest values of a list of 10 numbers.
I know you can use something like large(a1,a10,1)+large(a1,a10,2) if numbers are in an array but my problem is numbers arent
they are in random positions along a row....

can anyone helps?

thanks in advance

Dave

Hiya,

I am currently trying to calculate the largest gap in a series of numbers where a particular number has not appeared and display this number in a cell.

For example,

There are 2 numbers involved, 0 and 1. My spreadsheet looks like:

A1
A2 1
A3 0
A4 0
A5 0
A6 0
A7 1
A8 0
A9 0 and so on.....

Now, in cell C1 I want excel to automatically calculate the number of times 0 has come up consecutively up to the last time the number 1 appeared. So when inputting data downwards in A10, A11, A12 it will be able to tell me how many consecutive 0's there have been until the last number 1 appeared.

In the above example, if I had input the data upto A5, C1 would display the number 3 (there have been three 0's since the last 1 came up). When A6 is filled in with a 0, C1 would display 4, yet once A7 had been filled in the counter would reset back to 0 in C1.

I have tried to explain this as best as I can, if you need any clarification please ask, and thank you kindly in advance,

Dave

Heya,
I've been attempting to to make one formula that I can use the Large() formula on to return the largest value of an array, but I can't seem to get it condensed to work properly.

Item Units
A 5
A 2
B 10
C 1
B 3

So what I want is a formula to return the highest units of an item, so for A it would be 5, B it would be 10, & C would be 1. The only problem is there are duplicates of many items, and I only want their largest "Units" quantity. If anybody can point me in a good direction on which formulas you would use or examples and I'll run with it. Thanks.

Hello;

1) I need to calculate M43:: sum[m=1 to m=9] G(m)*sin(m*x) for each value of tabulated x in column L.
The 9 values of "m" are tabulated in $D$21:$D$29
The 9 values of G(m) are tabulated in $C$21:$C$29
The first x value is in cell L43

Can someone please help in deriving the array formula to be entered in M43 ??
For other values of x in L44, L45, ... one would simply copy M43 and paste in M44, M45, ...

2) If it's not too much trouble, how about:
N43:: -sum[m=1 to m=9] G(m)*cos(m*x)/m for each value of tabulated x

Your help would be greatly appreciated.

Regards.

there's a list of items by their colors and quantities. using LARGE function i can easily get the biggest quantity number out of the table. but how to tell excel to copy the color, in this case yellow, next to the quantity cell?

How to create a number series within an array formula, I mean, the series starts with 1 and will finish with 8 for example;
{1;2;3;4;5;6;7;8} - It will be the result within my array formula.
In Aij matrix means i=1 to 8 {i+1;i+1;i+1;i+1;i+1;i+1;i+1;i+1}.
I need to retrieve it within an array formula.

How can I do that just using an array formula.



Luthius

I have a list like this:

Sheet1

  A B C 1 Alfa Status D 75 2 Bravo Status A   3 Charlie Status B 483 4 Delta Status B 78 5 Echo Status C   6 Foxtrot Status A   7 Golf Status B   8 Hotel Status B 12 9 India Status A   10 Juliett Status A 405 11 Kilo Status C   12 Lima Status B   13 Mike Status A   14 November Status B 27 15 Oscar Status B 13 16 Papa Status B   17 Quebec Status C 140 18 Romeo Status B 426 19 Sierra Status C 484 20 Tango Status D 197

Excel tables to the web >> Excel Jeanie HTML 4

I want to single out all Status B values, and put them in a new table in descending order.

In this case, it would look like this:

Sheet1

  A B C 10 Charlie Status B 483 11 Romeo Status B 426 12 Delta Status B 78 13 November Status B 27 14 Oscar Status B 13 15 Hotel Status B 12

Excel tables to the web >> Excel Jeanie HTML 4

So I need to ignore blank values, determine which row has the highest quantity, and then return the value in column A and column C.

I know I can use array formulas to a certain extent, but I'm stumped as to how to store the row of the highest values in the array while ignoring blanks. Any ideas?