problems with a range

[holybull]

i want to be able to write a formula without having to manually change the range. ex
A B C
1 80 large(B1:B3,1)
1 75
1 85
2 60
2 25
my problem in the function above is i only want the numbers in column B that have the number 1 in column A. right now i am manually changing the range, i scroll down to the number 2 in column A and change the range . is there a simple solution to this....thanx

[NBVC]

You mean something like this?

Please Login or Register  to view this content.

[holybull]

it works, thanks but is there a way that the match can reconize the number2,3,4 etc from column A without having to manually change the match # column in the formula below, i still have to change the number 1 then scroll down to number 2..i want to be able to write the formula at the top of the column once without having to change the range...... if possible

[NBVC]

So are you going to have multiple formulas (one for each number in colA)?

if not, what determines the change?

[holybull]

the numbers in column A determine the range. (column A) the number 1 will be 5 rows then the number 2 will have 10 rows and number 3 will have 12 rows the number 4 will have 15 rows etc. i want to write a LARGE function in column C that will only give me the LARGE number with the corresponding 1 from column A, then the Large number with the corresponding 2 from column A etc. and i want to just write it once in column C and just scroll down without having to manually change the range.

[NBVC]

Maybe I am dense today? I still don't quite understand what exactly you need.

How is the one formula supposed to determine what number from column A, you want to find the largest for, if not manually inputted somewhere.?

[holybull]

it's not you, i didn't do a good job of explaining myself. i am a horserace handicapper and i'm bringing the racing form into excell.the first problem i had was the race number(column A) with my speed number(column B) and i wanted to write a LARGE function in column C to show the highest number in race 1(columnA) without referring to race 2(also column A), i played around with your solution and i got it...after match in the formula i just put A1 instead of just 1....one more problem and i promise i won't bug you anymore...can i do this with text? for example put text in column A, will it still work? thanx again

[NBVC]

As long as the strings are grouped together it should work by just replacing the 1 in the Match() function to a text string enclosed in double quotes or refer to a cell housing the string as you said you did for the previous problem:

e.g. If X is the string.

=LARGE(B1:INDEX(B:B,MATCH("X",A:A)),1)

Note: Those formulas will always start calculates at row 1, though. So if you're looking for Largest in the groups of 2's or 3's or y's, etc.... you will need to revise formula to something like below. This will find first 2 and last 2 and find largest in that group.

=LARGE(INDEX(B:B,MATCH(2,A:A,0)):INDEX(B:B,MATCH(2,A:A)),1)