[Solved] Max Value for Whole Column
-
- Posts: 56
- Joined: Sat Oct 11, 2008 2:51 pm
- Location: Oakland County, Michigan, USA
[Solved] Max Value for Whole Column
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?
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?
Last edited by dbrennan3333 on Fri Feb 27, 2009 6:58 am, edited 1 time in total.
OOo 2.4.X on Ms Windows XP
Re: Max Value for Whole Column
=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).
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).
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
-
- Posts: 56
- Joined: Sat Oct 11, 2008 2:51 pm
- Location: Oakland County, Michigan, USA
Re: Max Value for Whole Column
Keme,
Thanks a ton for your effort. I'll toy around with it right now and let you know how it goes.
Thanks a ton for your effort. I'll toy around with it right now and let you know how it goes.
OOo 2.4.X on Ms Windows XP
-
- Posts: 56
- Joined: Sat Oct 11, 2008 2:51 pm
- Location: Oakland County, Michigan, USA
Re: Max Value for Whole Column
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).
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).
OOo 2.4.X on Ms Windows XP
Re: Max Value for Whole Column
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)
=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)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 56
- Joined: Sat Oct 11, 2008 2:51 pm
- Location: Oakland County, Michigan, USA
Re: Max Value for Whole Column
Your guys' help is so greatly appreciated. OpenOffice continues to be a great blessing to me!
OOo 2.4.X on Ms Windows XP
Re: Max Value for Whole Column
keme wrote:=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.
OpenOffice 4.1.3 on Windows
Re: [Solved] Max Value for Whole Column
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.
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.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: [Solved] Max Value for Whole Column
Zizi64 wrote: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!
OpenOffice 4.1.3 on Windows
Re: [Solved] Max Value for Whole Column
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
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
LibreOffice 7.3.7. 2; Ubuntu 22.04