Re: [libreoffice-users] how to generate a re-scaled column
>>> "AP" == Andrew Pitonyak writes: Hi Thanks very much for your answer, it helped me a lot, but let me reply to your points below. > Uwe Brauer > Your question is a good one. > Either I misunderstand your question or you are doing something I do > not know (so you can teach me something) and I will provide what I > think can be a solution to your problem. Let me start with what I do > not understand. > Where you have B1/max(A2:A7), I actually expected it to be A2:A8, but You are absolutely right! I was so distracted (and annoyed that out of the sudden the entries in the max function changed (you explained below why this is so), that I did not pay attention to the other changes that orrur So the behavior I wanted to report should be like this | A | B | |---+| | 7 | =A1/max(A1:A7) | | 2 | =A2/max(A2:A8) | | 3 | =A3/max(A3:A9) | | 4 || | 5 || | 6 || | 1 || While I wanted | A | B | |---+| | 7 | =A1/max(A1:A7) | | 2 | =A2/max(A1:A7) | | 3 | =A3/max(A1:A7) | | 4 || | 5 || | 6 || | 1 || Now you already gave me the answer, using $. I tried this before sending the question, but not with the correct syntax! So the correct way of doing this, is: | A | B| |---+--| | 7 | =A1/max(A$1:A$7) | | 2 | =A2/max(A$1:A$7) | | 3 | =A3/max(A$1:A$7) | | 4 | | | 5 | | | 6 | | | 1 | | So I learned something very important thank you again. Now to the «dragged a formula» this is basically the same as you do via copying and pasting. So if I go back to my original question: | A | B | |---+| | 7 | =A1/max(A1:A7) | | 2 || | 3 || | 4 || | 5 || | 6 || | 1 || So I have just defined a formula in one cell. Instead of copying and pasting, I take the mouse at the right lower corner =A1/max(A1:A7) ^(mouse) And drag it down obtaining | A | B | |---+-| | 7 | =A1/max(A1:A7) | | 2 | =A2/max(A2:A8) | | 3 | =A3/max(A3:A9) | | 4 | =A4/max(A4:A10) | | 5 | =A5/max(A5:A11) | | 6 | =A6/max(A6:A12) | | 1 | =A7/max(A7:A13) | > I have never actually "dragged a formula" so some of what I am about > to say could be wrong. Also, perhaps you can tell me how to "drag with > the mouse". My best guess is that either (1) there is a feature I do > not know or (2) you are doing some kind of selection while entering a > formula (which I know can be done but again, somthing I do not usually > do). I usually drag with a mouse when I am simply selecting cells, but > I am unser how to drag a formula. Then again, I also do not remember > how to do array formulas (I think that is what they call them) even if > I did play with them once some years back. regards Uwe Brauer -- Warning: Content may be disturbing to some audiences I strongly condemn Putin's war of aggression against the Ukraine. I support to deliver weapons to Ukraine's military. I support the NATO membership of the Ukraine. I support the EU membership of the Ukraine. https://addons.thunderbird.net/en-US/thunderbird/addon/gmail-conversation-view/ -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] how to generate a re-scaled column
I think you can do =a1/max($a). I'm not sure of the format but you can specify shortform for the whole row. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] how to generate a re-scaled column
On Monday, October 02, 2023 09:02 EDT, Uwe Brauer wrote: So I thought | Marks | Scaled | |---+| | 1 | =A1/max(A1:A7) | | 2 | | | 3 | | | 4 | | | 5 | | | 6 | | | 7 | | That gives the correct entry for B1, however if now, drag with the mouse down that formula I obtain | Marks | Scaled | |---+| | 7 | =A1/max(A1:A7) | | 2 | =B1/max(A2:A7) | | 3 | | | 4 | | | 5 | | | 6 | | | 1 | | Which is not what I want. That is a very elementary question, but google does not help me. regards Uwe Brauer Your question is a good one. Either I misunderstand your question or you are doing something I do not know (so you can teach me something) and I will provide what I think can be a solution to your problem. Let me start with what I do not understand. Where you have B1/max(A2:A7), I actually expected it to be A2:A8, but I have never actually "dragged a formula" so some of what I am about to say could be wrong. Also, perhaps you can tell me how to "drag with the mouse". My best guess is that either (1) there is a feature I do not know or (2) you are doing some kind of selection while entering a formula (which I know can be done but again, somthing I do not usually do). I usually drag with a mouse when I am simply selecting cells, but I am unser how to drag a formula. Then again, I also do not remember how to do array formulas (I think that is what they call them) even if I did play with them once some years back. All that said, how I normally solve this problem would be as follows: 1. Enter the formula =A1/max(A1:A7) in cell B1 2. Select cell B1 and copy it to the clipboard. 3. Select cells B2:B7, and choose paste. The problem is that every cell reference is considered relative to the cell containing the formula. If I copy cell B1 and paste into cell B2, the column has not changed so column references stay the same but the row is now one greater so every row reference will increase by one. So in cell B2 I expect to see =A2/max(A2:A8) Paste into cell C2, so the row is one greater and the colun is one greater so the formula is =B2/max(B2/B8) All referenced cells are assumed to be relative to the cell where the formula is entered. If you place a dollar sign $ before a row or column in an address then it is taken to be an absolute address. So if I change the formula in cell B1 to be =A1/max(A$1:A$7) If I copy that formula from Cell B1 and paste that into cell D3, I have gone right two columns and down two rows so it will try to add two to every row and every column and I end up with the formula =C3/max(C$1:C$7) In your case this would have been sufficient because you were copying into cells B2:B7 and did not change the column. You could enter this as =$A1/max($A$1:$A$7) then the formula will always refence column A and only the very first row will increment because it is not preceded by a $. Note that if I insert a row into the middle, cell A7 becomes A8 and all references to A7, or even $A$7 will become $A$8. Finally, a cell address can include the sheet name, and the same applies to sheet names. My default sheet name is "Sheet1", so, I can set my formula to be =Sheet1.A1/max(A1:A7) If I copy this and paste it into cell B2 on Sheet 2 then my formula changes to =Sheet2.A1/max(A1:A7) If I had used $Sheet1.A1/max(A1:A7) then the first reference would still reference $Sheet1 Andrew Pitonyak -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] how to generate a re-scaled column
Wade Smart's answer is your solution. The reason is that you will freeze each component of the address of the reference for the maximum function. That is, $A freezes the column, $1 freezes the row within the column. By freezing the max function's reference, you allow the scaled cell to advance as you pull the top cell in column B down to auto increment. $ will freeze column, row or sheet as needed. On 10/2/23 8:02 AM, Uwe Brauer wrote: Hi I have the following simple table | Marks | Scaled | |---+| | 1 || | 2 || | 3 || | 4 || | 5 || | 6 || | 7 || In the column scaled I want each value from column Marks, divided by the maximum of column marks which is 7. So I thought | Marks | Scaled | |---+| | 1 | =A1/max(A1:A7) | | 2 || | 3 || | 4 || | 5 || | 6 || | 7 || That gives the correct entry for B1, however if now, drag with the mouse down that formula I obtain | Marks | Scaled | |---+| | 7 | =A1/max(A1:A7) | | 2 | =B1/max(A2:A7) | | 3 || | 4 || | 5 || | 6 || | 1 || Which is not what I want. That is a very elementary question, but google does not help me. regards Uwe Brauer -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] how to generate a re-scaled column
Try =A1/MAX($A$1:$A$7) -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 On Mon, Oct 2, 2023 at 8:13 AM Uwe Brauer wrote: > > > Hi > > I have the following simple table > > | Marks | Scaled | > |---+| > | 1 || > | 2 || > | 3 || > | 4 || > | 5 || > | 6 || > | 7 || > > > In the column scaled I want each value from column Marks, divided by the > maximum of column marks which is 7. > > > So I thought > > | Marks | Scaled | > |---+| > | 1 | =A1/max(A1:A7) | > | 2 || > | 3 || > | 4 || > | 5 || > | 6 || > | 7 || > > That gives the correct entry for B1, however if now, drag with the mouse > down that formula I obtain > > | Marks | Scaled | > |---+| > | 7 | =A1/max(A1:A7) | > | 2 | =B1/max(A2:A7) | > | 3 || > | 4 || > | 5 || > | 6 || > | 1 || > > > > Which is not what I want. > > That is a very elementary question, but google does not help me. > regards > > > Uwe Brauer > > > -- > Warning: Content may be disturbing to some audiences > I strongly condemn Putin's war of aggression against the Ukraine. > I support to deliver weapons to Ukraine's military. > I support the NATO membership of the Ukraine. > I support the EU membership of the Ukraine. > https://addons.thunderbird.net/en-US/thunderbird/addon/gmail-conversation-view/ > > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette > List archive: https://listarchives.libreoffice.org/global/users/ > Privacy Policy: https://www.documentfoundation.org/privacy > -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] how to generate a re-scaled column
Hi I have the following simple table | Marks | Scaled | |---+| | 1 || | 2 || | 3 || | 4 || | 5 || | 6 || | 7 || In the column scaled I want each value from column Marks, divided by the maximum of column marks which is 7. So I thought | Marks | Scaled | |---+| | 1 | =A1/max(A1:A7) | | 2 || | 3 || | 4 || | 5 || | 6 || | 7 || That gives the correct entry for B1, however if now, drag with the mouse down that formula I obtain | Marks | Scaled | |---+| | 7 | =A1/max(A1:A7) | | 2 | =B1/max(A2:A7) | | 3 || | 4 || | 5 || | 6 || | 1 || Which is not what I want. That is a very elementary question, but google does not help me. regards Uwe Brauer -- Warning: Content may be disturbing to some audiences I strongly condemn Putin's war of aggression against the Ukraine. I support to deliver weapons to Ukraine's military. I support the NATO membership of the Ukraine. I support the EU membership of the Ukraine. https://addons.thunderbird.net/en-US/thunderbird/addon/gmail-conversation-view/ -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
