Here is a formula that could help you.
=IF(SUMIF($A$8:A10,A11,$B$8:B10)<INDEX($B$3:$B$5,MATCH($A11,$A$3:$A$5,0)),IF(INDEX($B$3:$B$5,MATCH($A11,$A$3:$A$5,0))-SUMIF($A$8:A10,A11,$B$8:B10)<INDEX($D$3:$D$5,MATCH($A11,$A$3:$A$5,0)),INDEX($B$3:$B$5,MATCH($A11,$A$3:$A$5,0))-SUMIF($A$8:A10,A11,$B$8:B10),RANDBETWEEN(INDEX($C$3:$C$5,MATCH($A11,$A$3:$A$5,0)),INDEX($D$3:$D$5,MATCH($A11,$A$3:$A$5,0)))),0)
As you did not supply us with your actual data set, I had to create one of my own.
It might be different than yours but I needed someting to work with.
You can take a look at the attached workbook to see how the data are structured.
I'm sure you'll be able to modify the formula to suit your real data.
Regards
Bookmarks