Random numbers from specific range with fixed sum in Excel

Hello,
I'm a school teacher and recently I lost my specific marks. I only have the total marks with me. I'm desperately looking for formula to divide / distribute marks RANDOMLY from the total to each cell by SPECIFIC RANGE? Any kind soul please help me.

TnxNd8K.jpg

Download excel file: https://drive.google.com/open?id=1kj...Djx9JvcFpaU3SK

C2 and copy down/right:

Formula:

=RANDBETWEEN(MAX(0,2*$B3-SUM($B3:B3)-SUM(D$2:$L$2)),MIN(C$2,2*$B3-SUM($B3:B3)))

Note that for students: 8, 17, 18 you will get errors, because they collected 60 points, while max available total was 56 - please next time prepare REPRESENTATIVE sample data.

Also note that this formula refers one column right from your table, (column L), so for second sheet it shall be:

Formula:

=RANDBETWEEN(MAX(0,2*$B3-SUM($B3:B3)-SUM(D$2:$H$2)),MIN(C$2,2*$B3-SUM($B3:B3)))

(up to column H)

Glad it worked for you, and thanks for marking SOLVED and for reputation.


As for problems with attaching files:
Unfortunately attachment icon do not work at the moment , so next time after writing a post, but before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.