[Solved] Max Value for Whole Column

[dbrennan3333]

This MIGHT seem like a very primitive question, but I'm serious and haven't been able to find a solution any place else:

I'm simply looking to make the value of a cell dependent upon whether a random number generator in the column next to produced a higher number or a lower number.

For instance, I currently have:

=IF(a2>a1; a2; a1)

But what I need is for the cell to be the highest value IN THE ENTIRE COLUMN. So rather than it being merely about cells a1 and a2, I'd like it to be for ALL the cells in column a. Does anybody know how to do this?

Thanks in advance.

-----

NOTE: I've seen that in Google's spreadsheet (and also in Excel, I think), the function that I'm trying to perform would be executed with this simple command:

=max(a:a)

However, OpenOffice doesn't accept "a:a". So how do you achieve this same thing?

[keme]

=MAX(A$1:A$65536)
Or you could give the cell range a name, say "Entire_column_A", then =MAX(Entire_column_A) should do the same, and makes it easier to fix a situation where you change to different spreadsheet software (different limit to the number of rows).

[dbrennan3333]

Keme,

Thanks a ton for your effort. I'll toy around with it right now and let you know how it goes.

[dbrennan3333]

Okay, there are two unresolved matters remaining:

1) What is the process for naming a column?

2) In my original description of the problem, I was unclear. Your answer solved for the question that I did actually ask, but what I meant was:

How can I have a cell search for the max value in every cell above it within its column. For instance:

For cell "A2"
>I'd like "A1" to be searched

For cell "A5"
>I'd like cells "A1:A4" to be searched.

Currently, I have it reading like this:

=IF(A4>A3; A4/10; MAX(A1:A3)

The problem with this is that when I "cut-and-paste" that formula down the rest of column A, it doesn't automatically sense my goal of searching for the max value in all prior cells in the column.

Thanks in advance (again).

[Villeroy]

The old spreadsheet traditionals about [Tutorial] Absolute, relative and mixed references
=MAX($A$1:$A1)
due to a bug in v3.0 (fixed in 3.0.1) you have to use $A$1 in the first cell and then $A$1:$A2 in the next (3.0 changes $A$1:$A1 to $A$1:$A$1)

[dbrennan3333]

Your guys' help is so greatly appreciated. OpenOffice continues to be a great blessing to me!

[NewOOfan]

=MAX(A$1:A$65536)
Or you could give the cell range a name, say "Entire_column_A", then =MAX(Entire_column_A) should do the same, and makes it easier to fix a situation where you change to different spreadsheet software (different limit to the number of rows).

I love tools like Open Office so much. I know this is an old thread, but could somebody tell me how to list the NAME of that cell with the highest value instead of the actual value?


For example, I used the code above and it tells me the top value like $5. Cool. However could I get it to tell me WHERE that highest value is instead? Like, "C12" instead of $5?

Or one step further, could I have it instead of saying "C12" could I have it use the label I have in row 1? Which in my case is a date.

Not sure how this forum works if it will bump this old thread or not, but I'll favorite this and check.

[Zizi64]

Try to use the MATCH() or the VLOOKUP() function. It will find the first instance of the maximum value.
The MATCH() will return with an index number, and then you will able "calculate" the absolute name of the cell based on the index number.
The VLOOKUP() returns with the adat of the i-th column of the input range, when the i is an input parameter of the Vlookup. You can use - foe example - a helper table with the cell absolute names in the i-th column.

Please upload an ODF type sample file here, then we will able demonstrate the usage of the funcion MATCH() and VLOOKUP() on your datastructure.

[NewOOfan]

Try to use the MATCH() or the VLOOKUP() function. It will find the first instance of the maximum value.
The MATCH() will return with an index number, and then you will able "calculate" the absolute name of the cell.
The VLOOKUP() returns with the adat of the i-th column of the input range, when the i is an input parameter of the Vlookup. You can use - foe example - a helper table with the cell absolute names in the i-th column.

Please upload an ODF type sample file here, then we will able demonstrate the usage of the funcion MATCH() and VLOOKUP() on your datastructure.

You're the man Zizi. I love forums, so helpful.

I'm mostly a rookie to Excel and OO type software, I'll try to play around with what you said above to see if I can get it. I want to avoid re-constructing this doc with fake data just to upload.

Here's a summary of what I'm trying to do specifically if this helps. It's a simple doc really, dates on the left and dollar amounts to the right and basic sum functions in a few columns. Column A has dates in DD/MM/YEAR format. Ultimately, in column M row 2 I'm trying to get that cell to show me which row in column J has the highest sum. I successfully got that with the help earlier in this thread, but instead of telling me the sum in that J cell, I want it to tell me what the date is from column A that matches the highest sum in row J.

Hope that made sense. I'll pass on the help one day!

[jrkrideau]

Hi NewOOfan ,

Welcome to thet forum. I second Tibor (zizi)'s suggestion. Please upload a small sample file so that readers here can get a better grasp on what you are doing.

There is a Upload tab at the bottom of the Quote and Reply windows. An uploaded file is limited to 128k so if your file is larger, you should upload to a file sharing site, MediaFire and DropBox are both good choices but any will do.

If you are very new to spreadsheets, it can be a good idea to borrow a copy of some how-to book on spreadsheets from your local library or pick one up in a used bookstore. For basic operations, any book on just about any spreadsheet brand will do. The princples are the same.

The OOo manual for Calc can be found here. It is for 3.x but not much has changed for 4.x. https://wiki.openoffice.org/wiki/Docume ... Calc_Guide