Apportionment

[Pekis]

(05-24-2019 10:12 AM)pier4r Wrote:  Would it be possible for you to convert the excel formulas (that are harder to read with constant cells like $a$8 and cell operations like ROW) to normal formulas using a1, a2, a3, or whatever? (maybe even without one line if constructs)

This may encourage the discussion.

Well, without Excel, it would give (in a pseudo-BASIC):

ITEM_COUNT=5
DECIMALS=2
DIM A(ITEM_COUNT)
A(1)=83
A(2)=57
A(3)=106
A(4)=87
A(5)=57
NEW_AMOUNT=498

A_SUM=0
FOR I=1 TO ITEM_COUNT
....A_SUM=A_SUM+A(I)
NEXT I...................In the example: A_SUM=390
OLD_AMOUNT=A_SUM

DIM B(ITEM_COUNT)
B_SUM=0
FOR I=1 TO ITEM_COUNT
....B(I)=ROUND(A(I)*(NEW_AMOUNT/OLD_AMOUNT),DECIMALS)
....B_SUM=B_SUM+B(I)
NEXT I...................In the example: B_SUM=497.98

ADJUSTMENT_VALUE=SIGN(NEW_AMOUNT-B_SUM)...................In the example: ADJUSTMENT_VALUE= (+1) (add 1 cent per adjustment)
ADJUSTMENT_COUNT=10^DECIMALS*ABS(NEW_AMOUNT-B_SUM)...................In the example: ADJUSTMENT_COUNT=2 (2 adjustments to do)
ITEMS_PER_ADJUSTMENT=INT(ITEM_COUNT/(ADJUSTMENT_COUNT))...................In the example: ITEMS_PER_ADJUSTMENT=2 (each 2 items: add 1 cent)

DIM C(ITEM_COUNT)
C_SUM=0
FOR I=1 TO ITEM_COUNT
....C(I)=B(I)
....IF (B_SUM<>NEW_AMOUNT) THEN
........IF (I MOD ITEMS_PER_ADJUSTMENT)=0 THEN...................In the example: Each 2 items
............C(I)=B(I)+ADJUSTMENT_VALUE...................In the example: Add 1 cent to the current item
........END IF
....END IF
....C_SUM=C_SUM+C(I)
NEXT I...................In the example: C_SUM=498 and items are adjusted