Autofill values

Discuss the spreadsheet application
Post Reply
User avatar
waidesworld
Posts: 9
Joined: Wed Dec 12, 2007 7:29 pm

Autofill values

Post by waidesworld »

I am looking for a formula to autofill cells to their max, and then to bring the balance to the next cell, auto fill that etc.

So let's say I have Columns B to F. Each one needs a "fill" of 100. I want to type a value into A1 (say, 245) and then the "fill" would be 100 to B1, 100 to C1 and then 45 to D1.

Then two steps to complicate it further, if A2 has a new value of 425, I want to finish filling D1, then with the balance of 55 in this case. Then apply the 300 across the board to B2, C2, D2. This would leave me with a balance of 70 to be applied to A3.

Or am I plain crazy? (Also an acceptable answer)
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Autofill values

Post by RusselB »

I don't know about crazy (I'm not a certified psychiatrist), and what you're asking can be done, but it's not simple and does have limitations.

Can you explain why you want/need a spreadsheet to generate numbers in that format? If we can know why you want to do this, we might have an alternative suggestion.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
waidesworld
Posts: 9
Joined: Wed Dec 12, 2007 7:29 pm

Re: Autofill values

Post by waidesworld »

Yeah simply put. I want to put in a value on the first column and then have the value allocated across to the others. It's a payment plan for a set of charities.
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Autofill values

Post by keme »

What do you want to happen with "overflow" and "underflow"?

What we might call overflow:
Can the initial amount be so high that it fills more than the designated columns? Perhaps multiple rows? How far do you want surplus to "trickle down"?
Should such overflow have effect for the next row only, or as far down as it goes?

What we might call "underflow":
Can the initial amount be so small that even multiple succeding rows do not fill the ending of the previous row? How far do you want to "carry up" a deficit?
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Autofill values

Post by RusselB »

waidesworld wrote:It's a payment plan for a set of charities.
You might want to consider looking at Calc's Financial Functions in the Help menu/file. I've looked at them many times, and there's 3 pages worth, so I'm going to suggest checking them all.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Post Reply