Results 1 to 4 of 4

20040310, 22:49 #1
 Join Date
 Aug 2002
 Location
 Leuven, Vlaanderen, Belgium
 Posts
 322
 Thanks
 9
 Thanked 0 Times in 0 Posts
Multiple condition Sum: syntax or logical error? (ALL)
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: 012000, 1200040000,... 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)
+ (MAXgrant * the number of communities which are so huge (>= 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 <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15> <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15> <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15> . 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$30 9=C7)*(Comm!$B$2:$B$309<T7/R7))*T7)+(SUM((Comm!$D$2:$D$309=C7)*(Comm!$B$2:$B$ 309>IF(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))*T11*L11)+(SUM((Comm!$B$2:$B$309>=T11/R11)*(Comm!$B$2:$B$309<=E11)*(Comm!$B$2:$B$309))*R 11*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?

20040311, 01:28 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Multiple condition Sum: syntax or logical error? (ALL)
What number is it supposed to give? It seems to be a valid number so the "fault" would seem to be logic. And since I am having a hard time getting my brain around the logic, I will make a suggestion:
I find instead of using megaformulas, it is often better to create the intermediate calcs and use them as refs in the calcs instead of repeating calcs within the megaformula. Even though you have cells with calcs it is cleaner, more understandable, and actually may be faster and use less memory.
And most importantly, it allows much easier debugging.
Steve

20040311, 09:40 #3
 Join Date
 Aug 2002
 Location
 Leuven, Vlaanderen, Belgium
 Posts
 322
 Thanks
 9
 Thanked 0 Times in 0 Posts
Re: Multiple condition Sum: syntax or logical error? (ALL)
Steve,
Sorry if my post wansn't clear enough and thanks for your wise answer & tip!
Now I realise that I was too concerned about limiting the amount of columns and structure in this already wide spreadsheet by using those large formulas. I would better have been more conscious about the trouble & time loss this caused. I'm 'splitting up' the calculation in all parts now and it appears that I'll manage it (otherwise you'll hear from me :).

20040311, 11:02 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Multiple condition Sum: syntax or logical error? (ALL)
Good. I was dreading starting the day, trying to work thru the formula again this morning <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.
Don't hesitate to post back for additional help.
Steve