Re: [libreoffice-users] how to generate a re-scaled column

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