Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Moving Monthly Formula (Excel 3.0)

    I need to create a formula in cells AB11 & AP11 that will adjust themselves based on data as it comes in month after month. Currently I have data in cells b11:f11 (Jan thru May). Next month when June data comes in I would like cells AB11 to sum the budget cells P11:U11 instead of P11:T11 AND AP11 to sum the variance in cells AD11:AI11 instead of AD11:AH11 (hopefully this can be done by formula and not VBA).

    Thank you
    Attached Files Attached Files

  2. #2
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving Monthly Formula (Excel 3.0)

    Assuming you replace your uncompleted months' with 0 instead of '0 (zero amount instead of text zero) this should work

    Cell AB11 =SUM(OFFSET(P11,0,0,,MATCH(0,$B11:$M11,0)-1))
    Cell AP11 =SUM(OFFSET(AD11,0,0,,MATCH(0,$B11:$M11,0)-1))

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving Monthly Formula (Excel 3.0)

    Alternatively, if you clear the cells for the months without data, you can use

    =SUM(OFFSET(P11,0,0,1,COUNT($B11:$M11)))

    and

    =SUM(OFFSET(AD11,0,0,1,COUNT($B11:$M11)))

  4. #4
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving Monthly Formula (Excel 3.0)

    Hi Hans,
    This seems to work pretty well except for the issue with the zero's (see cells AC14,15,etc.) Cell AC13 worked only because I entered zero values. If there a way to modify the formula only to account for the text zero's and not have to manually clear the months without data? BTW this is an essbase retrieve and it automatically brings in these text pesky zero's. Also, I am doing this for a client and I do not want them to do find replace to correct. Ideally the formula should be able to account for these zero's.

    Thank you.
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving Monthly Formula (Excel 3.0)

    <img src=/S/ranton.gif border=0 alt=ranton width=66 height=37>
    Why do these big database systems always insist on exporting data in a useless format?
    <img src=/S/rantoff.gif border=0 alt=rantoff width=66 height=37>

    Try this:
    <code>
    =IF(COUNTIF($C11:$N11,">0")>0,SUM(OFFSET(Q11,0,0,1 ,COUNTIF($C11:$N11,">0"))),0)
    </code>
    and
    <code>
    =IF(COUNTIF($C11:$N11,">0")>0,SUM(OFFSET(AE11,0,0, 1,COUNTIF($C11:$N11,">0"))),0)</code>

  6. #6
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving Monthly Formula (Excel 3.0)

    Hi Hans, once again it almost works...lol. This managed to get rid of the REF error however now the formula sums everything (Jan - Dec)?
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving Monthly Formula (Excel 3.0)

    You used "0" instead of ">0" as condition in the COUNTIF functions. I've attached a picture of what the formulas should look like, just in case...
    Attached Images Attached Images
    • File Type: png x.png (2.7 KB, 0 views)

  8. #8
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving Monthly Formula (Excel 3.0)

    Hi Hans, this seems to work now except it does not recognize negative values in the actuals section and therefore sums incorrectly the budget (see cell AC12 should be 11,750 not 9,400.

    Regards.
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving Monthly Formula (Excel 3.0)

    Try using "<>0" instead of ">0" in the formulas.

    There will still be situations where you get incorrect results. That's the fault of the stupid export format provided by Essbase. <img src=/S/burnup.gif border=0 alt=burnup width=31 height=31>
    Attached Images Attached Images
    • File Type: png x.png (1.9 KB, 0 views)

  10. #10
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving Monthly Formula (Excel 3.0)

    I actually tried before posting, however it did NOT work. Anyway I appreciate your help and patience.

    Regards,
    Aluislugo

  11. #11
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving Monthly Formula (Excel 3.0)

    Hans I was thinking why can't your original formula be revised with an AND statement i.e., IF(COUNTIF($B12:$M12,">0"(and(countif($b12:$m12,"< 0")......etc.

    If so how would that formula look like?

    Regards.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving Monthly Formula (Excel 3.0)

    Try this one:

    =IF(SUMPRODUCT(1*(VALUE($C11:$N11)<>0))>0,SUM(OFFS ET(Q11,0,0,1,SUMPRODUCT(1*(VALUE($C11:$N11)<>0)))) ,0)
    Attached Images Attached Images
    • File Type: png x.png (2.2 KB, 1 views)

  13. #13
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving Monthly Formula (Excel 3.0)

    Hi Hans, once again it seemed to work for col AC, however for col AQ there were a few errors (see highlighted cells in yellow). I think that the formula in AQ needs to be modified slightly to add in the correct totals.

    Regards.
    Attached Files Attached Files

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving Monthly Formula (Excel 3.0)

    Try

    =SUMPRODUCT((VALUE($C11:$N11)<>0)*Q11:AB11)

    in AC11, and

    =SUMPRODUCT((VALUE($C11:$N11)<>0)*AE11:AP11)

    in AQ11.

  15. #15
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving Monthly Formula (Excel 3.0)

    Hi Hans I did as you suggested but I still got errors in cells AC25,27,28 and AQ25,27,28 ?
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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