should be a quickie, but it bugs me since I can't find it and it seems like it would be useful.

I'd like to write a formula that returns the row number of the highest value in a column. I know I can conditionally highlight it, but I want the row #.

9/17/2008 2:32:16 AM

could use MAX to find the highest value and MATCH to find the index of that value in an array, which will match up with the row number, then add an offset if the array doesn't start on row one.

e.g. if the array is in A3:A14
=MATCH(MAX(A3:A14),A3:A14)+2

9/17/2008 7:26:48 AM

danke!

9/17/2008 8:22:08 AM

bitte

9/17/2008 9:11:03 AM

another random excel question:

If I have a file with lots of formulas and conditional formatting, is there anyway to autofill in formulas if someone inserts a new line? I always just copy the line from directly above and overwrite the user input (and it seems easy enough to realize to do), but ive had a few people who use my calc sheet wonder why they have gotten errors on a certain line, and I realized they were inserting a new line and then just entering the data, in the user input cells and of course none of the formulas are in the new line if you do that.

I told them just to copy and paste the previous line down and then overwrite the user input cells and all the calcs will work themselves out, but is there anyway for me to avoid this altogether and somehow have the formulas entered into the cells when a new line is created?

10/9/2008 3:31:19 PM

is the new line always created at the end of the used range, or can it be randomly anywhere in the middle of the range?

10/9/2008 3:38:40 PM

It certainly could be in the middle although id say the majority of the time someone else would be using my program they would need to add lines at the end in the case that they are running out of lines before the summary line. Mainly because their calculation had more line items than the previous calc they did that they "saved as" and started working from if that makes sense.

Er after saying that I just realized "the end" of the line items would technically be the middle of the spreadsheet since there are some summation lines etc. directly below the line items.

[Edited on October 9, 2008 at 3:47 PM. Reason : ]

10/9/2008 3:46:18 PM

doesn't sound very feasible to me, using normal excel methods

10/9/2008 4:03:02 PM

yea I figured it was a real long shot but I at least wanted to ask.

10/9/2008 4:07:35 PM