I have put this data in a google doc for ease of displaying. Please go here to view (make note of the comments below the data):
https://spreadsheets.google.com/spr...R29BVTh5eVBnNlE&single=true&gid=7&output=html
(or tell me if there is a better way to display it here)
Once you are familiar with my data; you can see that the average turnover for all collections my budget will allow is 3.85% (I used an array formula to determine this). I would like to use caps of no less than half this, and no more than twice this, for any component collection.
For example, 1/2 of 3.85% is 1.93%. So, I could easily make a column to increase anything that fell below that (LP mystery, SciFi, SS, Westerns) too 1.93. But then my total amount would exceed my budget. The trick is to get the other values (Fiction, Large Print Fiction, and Mysteries) to redistribute proportionately, AND still fall within my max and min values.
Is there a way to make a new column of data that will automatically calculate to reflect this? Perhaps it needs to be done in several steps?
This is a simple data set, but in the future I will be doing a set with over a hundred of these, and it would be unwieldy to try and do them all manually.
I've wracked my brain on this and I can't solve it.
https://spreadsheets.google.com/spr...R29BVTh5eVBnNlE&single=true&gid=7&output=html
(or tell me if there is a better way to display it here)
Once you are familiar with my data; you can see that the average turnover for all collections my budget will allow is 3.85% (I used an array formula to determine this). I would like to use caps of no less than half this, and no more than twice this, for any component collection.
For example, 1/2 of 3.85% is 1.93%. So, I could easily make a column to increase anything that fell below that (LP mystery, SciFi, SS, Westerns) too 1.93. But then my total amount would exceed my budget. The trick is to get the other values (Fiction, Large Print Fiction, and Mysteries) to redistribute proportionately, AND still fall within my max and min values.
Is there a way to make a new column of data that will automatically calculate to reflect this? Perhaps it needs to be done in several steps?
This is a simple data set, but in the future I will be doing a set with over a hundred of these, and it would be unwieldy to try and do them all manually.
I've wracked my brain on this and I can't solve it.
Last edited:
