decrease integers to zero

[Andi]

I've looked around for this issue and can't seem to find one - my apologies
for any duplications.

At the top of Column B, I have a total amount. I have a formula based
calculation in Column B. Based on these numbers, I have a 'running total' at
the bottom of Column B. (B1-sum(B2:B17)). Some of the cells formuals come
to zero at the bottom cells of the column. I would like to add 1 to each
cell, starting with those that have a zero and then back to the top with the
higher numbers until the 'running total' is zero.

Here's what it looks like:
Total Available 62
A 16
B 12
C 9
D 7
E 5
F 0
G 0
H 0
I 0
J 0
K 0
L 0
M 0
N 0
O 0
P 0
Running Total 13
Here's what I want the end result to be, without having to manually enter
the information:

Total Available 62
A 17
B 13
C 9
D 7
E 5
F 1
G 1
H 1
I 1
J 1
K 1
L 1
M 1
N 1
O 1
P 1
Running Total 0


Any ideas? I keep running into circular reference issues that I'm just not
good at.
Any help is appreciated! This is part of a bigger revamp of a spreadsheet.

 

[Luke M]

You're going to have to use a second column, because of the circular logic
effect. However, you could hide the 1st column once you've got it setup,
giving the appearance of what you want.

In helper column (say, column C) setup your total the same as your B column.
In last cell before total (C17) input this formula:
=IF(B18>0,B17+1,B17)
In C16, input:
=B16+MIN(1,MAX(0,$B$18-COUNTA($C$17:C17)))
Copy this all the way up to the top of your numbers (in your example, would
be C2).

Note that to make things easier to update when you hide the column, you
should change B1 to reference C1 (=C1) so that you don't have to unhide it to
make a change.

 

[Andi]

Luke -

Thanks for the response. I tried this, and unfortunately it doesn't quite
work the way I need it to. I probably didn't explain well that it needs to
start at the 'top' of the zeros, if that makes sense. So, in the example
below, I need the additions to begin at F (row 6) and cycle to the bottom
(row 17) and then up to the top at B (row 2) again until the amount is zero.
There are several columns that I will need this to happen with, all with
different numbers and instances of zeros. For instance, there are some
columns that have an amount greater than zero in each cell, and in those
cases, I need to start at the top of the list and go top to bottom. If it
helps, the numbers are always in decreasing order from Row 2-17.

 

[Harlan Grove]

At the top of Column B, I have a total amount.  I have a formula based
calculation in Column B.  Based on these numbers, I have a 'running total' at
the bottom of Column B.  (B1-sum(B2:B17)).  Some of the cells formualscome
to zero at the bottom cells of the column.  I would like to add 1 to each
cell, starting with those  that have a zero and then back to the top with the
higher numbers until the 'running total' is zero.

....

It may be possible to do this with a single array formula in B2:B17,
but you'd need to show the formulas currently in B2:B17.

It could be done in C2:C17 using the formulas

C2:
=B2+INT(B$19/ROWS(B$2:B$17))+(MOD(B$19,ROWS(B$2:B$17))

MOD(COUNTIF(B$2:B$17,"0")-ROWS(B2:B$17),ROWS(B$2:B$17)))

Fill C2 down into C3:C17.

 

[Andi]

That's awesome, Harlan - thanks a bunch.

Here's the formula for Column B (column A has numbers in it).
=INT($B$20*A2).

 

[Andi]

This formula works very well, thanks again.

Does anyone have ideas on how to work the formula in column B? The formula
currently there is =INT($B$20*A2).
Obviously, there are numbers in column A and cell B20.

Thanks!!!

 

[Harlan Grove]

Does anyone have ideas on how to work the formula in column B?  The formula
currently there is =INT($B$20*A2).  

....

Select B2:B17 and enter the array formula

=INT($B$20*A2:A17)+INT((B1-SUM(INT($B$20*A2:A17)))/ROWS(B2:B17))
+(MOD((B1-SUM(INT($B$20*A2:A17))),ROWS(B2:B17))

MOD(SUMPRODUCT(--(INT($B$20*A2:A17)=0))+ROW(B2:B17)-MIN(ROW(B2:B17)),

ROWS(B2:B17)))

You could shorten this with some hardcoding, but that would require
revision if you move the range of array formulas to a different set of
rows.

As you can see from all the $B$20*A2:A17 terms, this is very
inefficient. Better to use 2 cells for each result, i.e., two ranges
for the single result range: the first containing your current =INT($B
$20*A2) formulas, the second containing the formulas in my previous
response.

 

[Andi]

Harlan -

Thanks. I think I will go with your suggestion to separate into two columns
as I am having difficulties getting the formula below to give me the numbers
I think I want. Thanks again for the time and the interesting formulas that
give me some good stuff to think about!