Newbie - Finding maximum in a variable range

[tcheung]

Hi guys,

I am a newbie in excel and vba. Apology in advance if the question look so stupid. I have read through some of the post but I could not find the answer I want.

I am now working on an excel spreadsheet which will automatically update every day so that one row will be added on the top of the sheet everyday.
i.e.
A1 Date Price
A2 (New row added,date) (New row added,price)
A3 16/1/2013 5.5
A4 15/1/2013 5.6
A5 14/1/2013 5.45
... ...

Assuming that I am looking for maximum value of the column "Price", since max() cannot be applied to variable range, is there other method I could useto look for the most updated maximum value of column "price"?

Thank you

 

[tcheung]

Would someone please help? Pretty urgent

 

[azumi]

Assuming Price Column start at Column B --> =LOOKUP(90000000000,B:B) Input The Formula at Colum A or C, and don't input at Column B

Another Formula to retrieve the last occurence :

=VLOOKUP(90000000000,B:B,1)

or

=LOOKUP(2,1/(B:B<>""),B:B)

Just try it at your case

Regards
Azumi

 

[azumi]

The formula is for numeric only bro....

 

[tcheung]

Assuming Price Column start at Column B --> =LOOKUP(90000000000,B:B) Input The Formula at Colum A or C, and don't input at Column B

Another Formula to retrieve the last occurence :

=VLOOKUP(90000000000,B:B,1)

or

=LOOKUP(2,1/(B:B<>""),B:B)

Just try it at your case

Regards
Azumi

Thanks for your reply Azumi.

Correct me if I am wrong. Formulas =VLOOKUP(90000000000,B:B,1) or =LOOKUP(2,1/(B:B<>""),B:B) will only recall the new data created everyday right?

Maybe let me clarify a bit on my question. I am looking for Maximum value of column B, which originally achieved by Max(B1:B100) function. However, New data will be inserted to B1 everyday such that original B1 will become B2 on next day, so as the other cell Bx becoming B(x+1) next day.

This means B1 itself is a variable (I am not sure if I am correct to describe it in this way). Max() function cannot be applied.

So i am looking for any way to determine the maximum value of column B, which is ever-changing day after day.

 

[tcheung]

Thanks for your reply Azumi.

Correct me if I am wrong. Formulas =VLOOKUP(90000000000,B:B,1) or =LOOKUP(2,1/(B:B<>""),B:B) will only recall the new data created everyday right?

Maybe let me clarify a bit on my question. I am looking for Maximum value of column B, which originally achieved by Max(B1:B100) function. However, New data will be inserted to B1 everyday such that original B1 will become B2 on next day, so as the other cell Bx becoming B(x+1) next day.

This means B1 itself is a variable (I am not sure if I am correct to describe it in this way). Max() function cannot be applied.

So i am looking for any way to determine the maximum value of column B, which is ever-changing day after day.

Maybe make it simpler:

How to find a maximum value in a column(let say "B") which contains variable (in this case, new data will be added to B1 everyday and pushes all the data down by 1 row), i.e. a replacement which works as worksheetfunction MAX(....) but work for range with variables

Note: the data adding function is determined by a terminal connected to the computer, and thus cannot be changed.

 

[Aladin Akyurek]

hi guys,

i am a newbie in excel and vba. Apology in advance if the question look so stupid. I have read through some of the post but i could not find the answer i want.

I am now working on an excel spreadsheet which will automatically update every day so that one row will be added on the top of the sheet everyday.
I.e.
A1 date price
a2 (new row added,date) (new row added,price)
a3 16/1/2013 5.5
a4 15/1/2013 5.6
a5 14/1/2013 5.45
... ...

Assuming that i am looking for maximum value of the column "price", since max() cannot be applied to variable range, is there other method i could useto look for the most updated maximum value of column "price"?

Thank you

=MAX(B:B)

should suffice.

 

[G_L_Excel]

Hi

Try this solution :


Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select


This code selects cell A1, then goes to the last row of column A, then all the way right to the last column

You can determine the Row and column of the active cell as follows:

Dim MaxRow,MaxColumn As Double

ActiveCell.Row = MaxRow
ActiveCell.Column = MaxColumn

Kind Regards

Gerry

 

[azumi]

Ok my bad, i'm misunderstood with your expecting results, the solution is easy bro, =MAX(B2:B1000) , then u can add up the new row for data until u reach the 1000th row

 

[G_L_Excel]

Ok my bad, i'm misunderstood with your expecting results, the solution is easy bro, =MAX(B2:B1000) , then u can add up the new row for data until u reach the 1000th row

OK Good luck with it. Should you have more than 1000 rows, you could use my solution.

Greetings

G.