decrease integers to zero
Andi wrote...
....
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.
|