# Newbie - Finding maximum in a variable range

Show 40 post(s) from this thread on one page
Page 1 of 2 12 Last
• Aug 6th, 2013, 11:51 PM
tcheung
Newbie - Finding maximum in a variable range
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
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
• Aug 7th, 2013, 12:38 AM
tcheung
Re: Newbie - Finding maximum in a variable range
• Aug 7th, 2013, 01:50 AM
azumi
Re: Newbie - Finding maximum in a variable range
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
• Aug 7th, 2013, 01:52 AM
azumi
Re: Newbie - Finding maximum in a variable range
The formula is for numeric only bro....
• Aug 7th, 2013, 02:14 AM
tcheung
Re: Newbie - Finding maximum in a variable range
Quote:

Originally Posted by 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

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.
• Aug 7th, 2013, 02:51 AM
tcheung
Re: Newbie - Finding maximum in a variable range
Quote:

Originally Posted by tcheung

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.
• Aug 7th, 2013, 03:05 AM
Re: Newbie - Finding maximum in a variable range
Quote:

Originally Posted by 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
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.
• Aug 7th, 2013, 03:10 AM
G_L_Excel
Re: Newbie - Finding maximum in a variable range
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
• Aug 7th, 2013, 06:37 AM
azumi
Re: Newbie - Finding maximum in a variable range
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
• Aug 7th, 2013, 07:03 AM
G_L_Excel
Re: Newbie - Finding maximum in a variable range
Quote:

Originally Posted by 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

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

Greetings

G.
Show 40 post(s) from this thread on one page
Page 1 of 2 12 Last