Re: [libreoffice-users] auto-populating cells with one figure increasing, and the second figure decreasing, but how, please?

Den ons 9 okt. 2024 kl 06:50 skrev Sharon Kimble <
boudiccas@skimble09.plus.com>:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA512
>
>
> I'm trying to construct a spreadsheet which will track my writing goals
> for a 90-day period and is of this format '=SUM(D17/76)' and the cell
> beneath it is '=SUM(D18/75)'.

I usually use ROW() for that. For instance ROW(A1) returns 1 and ROW(E459)
returns 459. In your example I would start with:
=SUM(D17/(77-ROW(D1)))

Now both D17 and D1 will increase (D18 D2, D19 D3 and so on) as you fill
down, so the first three rows will be equivalent to:
=SUM(D17/(77-1))
=SUM(D18/(77-2))
=SUM(D19/(77-3))

which in in turn are equivalent with:
=SUM(D17/76)
=SUM(D18/75)
=SUM(D19/74)

Just watch out when filling the other direction (up), since there's nothing
above D1. For instance ”=SUM(D17/(77-ROW(D1)))” will turn to
”=SUM(D16/(77-ROW(D#REF!)))” which will display ”#REF!”. A way to get
around that is of course to choose a higher number to subtract from:
=SUM(D17/(177-ROW(D101)))
Something like that. Now you can fill down (all the way) and up (maximum
100 rows).
So it's basically just simple maths. :)

Kind regards

Johnny Rosenberg




> So how can I get it such that the first figure is increasing, and the
> second figure is decreasing, and I can just drag the cells downwards and it
> auto-populates the cells as I'm trying to get them, please? Or in other
> words, at the
>   end of the 90-day period the second figure will be zero, please?
>
> Thanks
>   Sharon.
> - --
> A taste of linux = http://www.sharons.org.uk
> DrugFacts = https://www.drugfacts.org.uk
> Debian 12.7, Fluxbox 1.3.7, emacs 31.0.50, org 9.7.12
> -----BEGIN PGP SIGNATURE-----
>
> iQJRBAEBCgA7FiEELSc/6QwVBIYugJDbNoGAGQr4g1sFAmcGC3YdHGJvdWRpY2Nh
> c0Bza2ltYmxlMDkucGx1cy5jb20ACgkQNoGAGQr4g1uKkhAArA9d+H9SCNLZY5yi
> BjQ2qh0sQHlzoLltOxs9pdgt5t5WiGpnEvuCT2tlTa1hqsfwR1UvntG+DJyQHUyp
> SLaP0B5tyZxn+YizFhuqC1Mc6n8JddAmmLlP/+3vGFJ2gcchPCX/rOenctx8vPHy
> eGmyR4f3VCU8iqHerpxvJ3RHV1JSpHpF1X7dtpS92VhI1QiSBbDPnhZLVTK5U/gB
> elHcKFCaKUuPaWstNHGDwt5SJEJxt+ZahPhlgHMBnShGOYhzwO1vYhzN7lNQA80U
> bmt8WDNvPoD1yttLrOjRvTPPPpkkC9wJe7TH16h0Md5Xh9POpSXDnr3G5KFIuWjP
> 78GrtGMUb37GvDKOO5yROP75bBMLE90hMotVlHNaUegcWnhHLjABmpyTW0w3gzJf
> fhQ/FUSfX7yQdIJntvyAKEEVrYIslqzjFRYZsxnl6ekkHDsQTbto4Gfh/95UFmAM
> 4t+JBZw6pH7lrUePLuJsVW8KaIgq2xOKm0Dt9OCjk6RHUSyxpariwmA2KZDbWTKi
> Eagow9d16pB2+Wy8dvviII2eY4d4QoMQpvBpi1FlP7UZUYwVo5SKuY8bxn2Pb3LB
> Hcmz/so9vgVJJ18jVUwjNrxxzpX9ihuuVwsp4vRrKEc6aOuUPte79n+u+CQXMS3t
> 1MATQCZiFsYHimNelsbF10Qkn6w=
> =r19r
> -----END PGP SIGNATURE-----
>
> --
> 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

-- 
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