Thread: Funding allocation with maximum values

I think you need to explain this in a bit more detail. What are the "units" in your file? Are you allocating units or money? How is your average to be worked out? So many things I don't understand !!

Pete

Originally Posted by tigeravatar

blinkzorg,

Welcome to the forum!
In cell F5, use this formula:
=IF(SUM($F$4:F4)=$B$1,0,B5*MIN(E5,($B$1-SUM($F$4:F4))/SUM(B5:$B$13)))

And then copy down

Thanks for the welcome and the suggestion, tigeravatar. It works for the first cell, but when I copy it down, it adds up to more than the total amount allocated!

Originally Posted by Pete_UK

I think you need to explain this in a bit more detail. What are the "units" in your file? Are you allocating units or money? How is your average to be worked out? So many things I don't understand !!

Pete

Sorry for not being clear, Pete. So this is how it's supposed to work:

We start with the total allocated amount of funding (B1). This is the total amount to be distributed among the various categories below, to buy a certain number of units of each category. Ideally, I would like the user to be able to select which categories to include in the calculation by entering a 1 or a 0 in column B.

The "units" themselves are not actually important, just the money. In fact you can basically ignore the unit cost and cost per unit and just focus on the "Maximum Allocation" column. What I want to do is allocate funding from the total in B1 to each category with a "1" in the "Active" column. There's no priority for each category, so initially I want it to divide the total evenly among each category. For example, there are three active categories on the spreadsheet. Dividing the total funding into three, we have $6,566,667 allocated each to Categories 1, 2, and 5. But notice that this is a larger amount than the maximum allocation amount for category 1, $4,898,560. So I want $4,898,560 to appear in cell G5, and the difference allocated evenly among the remaining active categories. Does that make sense?

I've attached a file that does this as an iterative process using multiple columns. The initial allocation (column I) will ensure that no allocation exceeds the maximum allowed, but of course, this means that there may be some remaining funds which need to be allocated. The next iteration (column J) thus takes the remaining funds and adds them to the initial allocations, ensuring that no one alloction exceeds the maximum. In the example, the iteration stops there as all money has been distributed, but if one or more of the categories which received a second allocation then achieved the maximum allowed, there would still be some remaining money to be distributed. The formulae in column J (i.e. J4 to J13) are copied across into K4:K13 (and indeed could be copied further if the need arose, that is if the value in the cell on row 4 is not zero) in order to test for another iteration. In the example the values in column J are the same as those in column K, so no further iterations are required.

Hope this helps.

Pete

That's what I was thinking, that the allocation has to be iteratively determined, but I didn't know how to go about doing it... However, it seems like you forgot to attach the file!

No, the forum didn't let me and has crashed on me several times today. I'll try attaching it to this.

Pete

EDIT: It seems to have allowed it this time.

The file doesn't seem to work, even though I can download my own file from the initial post. perhaps you could send it via e-mail? androido42 [at] hotmail.com

File sent. I'll also try re-attaching it here.

Pete

Sorry for the delay! Thank you both very much, both of your methods worked like a charm!