decrease integers to zero
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!
"Harlan Grove" wrote:
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.
|