Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    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: 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)
    + (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?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    3 Star Lounger
    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 :-).

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •