Date: prev next · Thread: first prev next last
2023 Archives by date, by thread · List index

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.

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


Paste into cell C2, so the row is one greater and the colun is one greater so the formula is


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


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


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


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


If I copy this and paste it into cell B2 on Sheet 2 then my formula changes to


If I had used $Sheet1.A1/max(A1:A7) then the first reference would still reference $Sheet1

Andrew Pitonyak


To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
Privacy Policy:


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.