Excel Array Formula Series #4: Find Largest Improvement

Hi All

Is there a better way of me doing this?

I am currently trying to find out the number of items which are greater than or equal to 230 on each day of the year....i.e. if all items were greater than 230 on a particular day then the formula would return the value of 4.

There are only four items which does make the problem slightly easier. Currently I am using the formula below. This does appear to work although I do need to re-write the formula for each item. I am sure there must be a better way of doing this as this formula would not be much use if there were more than four items....possibly using an array formula?

Code:

=IF(AND(G9>=230,G1308>=230,G2607>=230,G3906>=230),4,IF(AND(G9>=230,G1308>=230,G2607>=230),3,IF(AND(G9>=230,G1308>=230,G3906>=230),3,IF(AND(G9>=230,G2607>=230,G3906>=230),3,IF(AND(G9>=230,G1308>=230),2,IF(AND(G9>=230,G2607>=230),2,IF(AND(G9>=230,G3906>=230),2,IF(G9>=230,1,0))))))))

I'm keen to find out the best way of doing this so any help would be greatly appreciated.

Thanks

Simon

I need to create a report with 1st and 2nd largest number in an array in column A and find corresponding date in the same row (Column B). I am able to use LARGE () function to grab both the 1st and 2nd largest and was hoping to use VLOOKUP() but the catch is that if both 1st and 2nd largest values are same, then I run the risk of retrieving related values from first match itself. In the sample data below, the largest value of 75 occurs twice and I run the risk of retrieving Jun-09 twice (instead of both Jun-09 & Sep-09).

Any help is appreciated.

E.g.

42 Mar-09
38 Apr-09
61 May-09
75 Jun-09
14 Jul-09
23 Aug-09
75 Sep-09
32 Oct-09

S47 is the name of a table.
R47 is the name of a smaller array in the table that is 12 rows by however many columns are in the named range.
P47 is the number of a row in the smaller array.

I use this formula to go to the row I want and find the 5 largest numbers in that one row

{LARGE(OFFSET(INDIRECT($R$47),$P$47-1,0,1,MAX(COLUMN(INDIRECT($R$47)))-2),1)}

Now I want to go to the same small array (R47) and add up each column in the array (there are 12 rows in the columns), then from those totals, find the 5 highest ones. Can I do this in one formula or do I need to do it in two formulas (1, add it up; 2, find the large)?

I'm thinking something like:
Large(SUM(INDEX(INDIRECT($R$47),0,0)),1) which doesn't work.

Thanks

I have a table with subtotals that I need to find the largest value for the subtotal results and then return the cell contents for the corresponding row.

I have attempted to use the hlookup function, but keep getting a #ref error (probably because I am just not that familiar with the entire formula requirements).

I attempted to nest in the 'largest' function to the lookup function, but have so far been stymied.
I've attempted the following: =HLOOKUP(D40,A2:D36,ROW(A2:C36))
where d40 is the formula =LARGE(D2:D36,1)
I suspect that I am failing miserably with the row() command (since I really don't understand it).
Attached is an example of the sheet.

I need to find the largest value within a range that is less than the result of a formula from another set of values.

Something like this:

=MAX(E98:E115) that is <=(P63+((1.5*(P63-P61))))

But this formula doesn't work.

Does anyone have any advice?

Cheers,
Z

Edit: I am using Office 2007.

In Row 5, I have a formula that gives me the Maximum value. I would like a formula in Row 6 that gives me the next largest value. Is it possible?


******** ******************** ************************************************************************> Microsoft Excel - UNHIT COMBOS AFTER 1000 DRAWS.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout B5 C5 D5 E5 B12 C12 D12 E12 B13 C13 D13 E13 B14 C14 D14 E14 B15 C15 D15 E15 B16 C16 D16 E16 B17 C17 D17 E17 B18 C18 D18 E18 B19 C19 D19 E19 B20 C20 D20 E20 B21 C21 D21 E21 =
B C D E 5 2168 1842 1638 1623 6 7 8 9 10 11 12 68 112 82 1472 13 511 224 115 395 14 239 1276 732 1623 15 670 1732 1403 940 16 2168 1455 875 254 17 702 206 1638
18 534 1842 911
19
18 265
20

9
21

985
COMBOS
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

In Row 5, I have a formula that gives the Maximum value in each column. I would like a formula in Row 6, that will geive the next largest value. Is this possible?




******** ******************** ************************************************************************> Microsoft Excel - UNHIT COMBOS AFTER 1000 DRAWS.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout B5 C5 D5 E5 B12 C12 D12 E12 B13 C13 D13 E13 B14 C14 D14 E14 B15 C15 D15 E15 B16 C16 D16 E16 B17 C17 D17 E17 B18 C18 D18 E18 B19 C19 D19 E19 B20 C20 D20 E20 B21 C21 D21 E21 =
B C D E 5 2168 1842 1638 1623 6 7 8 9 10 11 12 68 112 82 1472 13 511 224 115 395 14 239 1276 732 1623 15 670 1732 1403 940 16 2168 1455 875 254 17 702 206 1638
18 534 1842 911
19
18 265
20

9
21

985
COMBOS
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Hey guys
My table is like this (but more rows than this ex):

Col A Col B Row 1 Items Value Row 2 Item A 105 Row 3 Item B 96 Row 4 Item C 105 Row 5 Item A 75 Row 6 Item B 76,8 Row 7 Item A 45 Row 8 Item D 78 Row 9 Item A 36 Row 10 Item E 120 Row 11 Item C 119

I now want to make a formula that respons "Max" i column C for each maximum value of the item "n" - like this:

Col A Col B Col C Row 1 Items Value Max Row 2 Item A 105,0 Max Row 3 Item B 96,0 Max Row 4 Item C 105,0 Row 5 Item A 75,0 Row 6 Item B 76,8 Row 7 Item A 45,0 Row 8 Item D 78,0 Max Row 9 Item A 36,0 Row 10 Item E 120,0 Max Row 11 Item C 119,0 Max

I'm sure this can be done, but how?

Will anyone please guide me on this one?