On Monday, October 02, 2023 09:02 EDT, Uwe Brauer <oub@mat.ucm.es> 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+unsubscribe@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
Context
Privacy Policy |
Impressum (Legal Info) |
Copyright information: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License.
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (
MPLv2).
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our
trademark policy.