Dear members of the board, can anyone help me solving this problem?
I need to estimate the budget needed for granting communities (local governments) which have signed a covenant (contract) with us.
This calculation is based on:
– an estimate of the percentage of communities signing in (sign%);
– a grant amount of …$/inhabitant (grant/inh), with an optional minimum (MINgrant) & maximum (MAXgrant).
(This calculation goes for a number of projects from which the community can choose to participate in.)
As larger communities tend to sign more often, there was a large bias on the calculation. Therefore, communities were divided in size classes, like: 0-12000, 12000-40000,… As such, each class could be given it’s own % of signings and even it’s own grant, minimum and maximum amount. This reduced the ‘bias’ significantly.
I calculate the grant estimate for each class as being:
= sign% * ((MINgrant * the number of communities which are so small (== MAXgrant/grant/inh) that they fall into this category)
+ (grant/inh * the sum of the inhabitants of all other communities ‘in between’))
Each term requires a multiple condition array count or sum, based on the community tab data.
To achieve this, I use two tabs:
– calculation tab: a list of projects (each occupying as many rows as there are classes) with from left to right through this row the different calculation steps and intermediate results…
– community tab: a list of all communities, their population and (calculated) the size class they belong in, etc.
Now I can’t get the formula’s right! I’ve spent day’s building up the whole system and now I got stuck in one formula 
. Far too many hours I’ve plunged into searching why… So, any help is really appreciated. Did I use the wrong syntax in the formula (althoug it gave a ‘valid’ result) or if I make a logical mistake?
This is the actual formula:
{=L7*IF(AND(T7=0;U7=0);G7*R7;(SUM((Comm!$D$2:$D$309=C7)*(Comm!$B$2:$B$309IF(U7=0;1000000000;U3/R7))*U7)+(SUM((Comm!$D$2:$D$309=C7)*(Comm!$B$2:$I$309>=T7/R7)*(Comm!$B$2:$B$309<=IF(U7=0;1000000000;U7/R7))*(Comm!$B$2:$B$309))*R7)))}
with L = sign%
G = total # inhabitants of the class
R = grant/inh
T = MINgrant
U = MAXgrant
…!$B$2:$B$309 = data range of individual community's #inhabitants
…!$B$2:$B$309 = data range of individual community's size class
I've also attached a sample…
ps The funny thing is that first, I got my formulas working quite all right (as far as I tested them) using a simpler scheme, only allowing one MAXgrant (in the highest class) and one MINgrant (in the lower class)! In the lowest class' calculation, I took only into account the MINgrant. There, the formula looked like:
{=IF(T11=0;N11*R11;(COUNTIF(Comm!$B$2:$B$309;"=T11/R11)*(Comm!$B$2:$B$309<=E11)*(Comm!$B$2:$B$309))*R11*L11))}
with L = sign%
N = total # inhabitants of the class including sign% (so N = L* G above)
R = grant/inh
T = MINgrant
…!$B$2:$B$309 = data range of individual community's #inhabitants
In the other, higher classes the formula's was similar, but taking into account only the MAX/grant/inh. And those formula's seemed to work all right! So, where did I go wrong?
.
