Results 1 to 15 of 18

20080618, 23:25 #1
 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

20080618, 23:41 #2
 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))

20080619, 00:35 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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)))

20080619, 15:11 #4
 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.

20080619, 15:22 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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>

20080619, 15:36 #6
 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)?

20080619, 15:53 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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...

20080619, 16:47 #8
 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.

20080619, 16:55 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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>

20080619, 17:05 #10
 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

20080619, 17:46 #11
 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.

20080619, 17:48 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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)

20080619, 18:15 #13
 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.

20080619, 18:33 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20080619, 18:54 #15
 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 ?