Moving Monthly Formula (Excel 3.0)

Topic

New Reply

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

Reply | Quote

Replies

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))

Reply | Quote

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)))

Reply | Quote

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.

Reply | Quote

Why do these big database systems always insist on exporting data in a useless format?

Try this:

=IF(COUNTIF($C11:$N11,">0")>0,SUM(OFFSET(Q11,0,0,1,COUNTIF($C11:$N11,">0"))),0)

and

=IF(COUNTIF($C11:$N11,">0")>0,SUM(OFFSET(AE11,0,0,1,COUNTIF($C11:$N11,">0"))),0)

Reply | Quote

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)?

Reply | Quote

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…

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Regards,
Aluislugo

Reply | Quote

Try this one:

=IF(SUMPRODUCT(1*(VALUE($C11:$N11)0))>0,SUM(OFFSET(Q11,0,0,1,SUMPRODUCT(1*(VALUE($C11:$N11)0)))),0)

Reply | Quote

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.

Reply | Quote

Try

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

in AC11, and

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

in AQ11.

Reply | Quote

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

Reply | Quote

You’re getting 0’s there because all the “Actual” values in those rows are 0. How in heaven’s name can we determine which columns should be used if a 0 can both mean “the actual value is 0” and “there are no data yet”?
You should get the Essbase people to deliver useful data, or sue them.

Reply | Quote

Point well taken my friend!

Nevertheless thank you once again for all your help.

Regards,
aluislugo

Reply | Quote

Assuming that the totals row C29:N29 will contain a non-zero value for the months with data and zero for the months without data, you could use

=IF(SUMPRODUCT(1*(VALUE($C$29:$N$29)0))>0,SUM(OFFSET(Q11,0,0,1,SUMPRODUCT(1*(VALUE($C$29:$N$29)0)))),0)

in AC11, and

=IF(SUMPRODUCT(1*(VALUE($C$29:$N$29)0))>0,SUM(OFFSET(AE11,0,0,1,SUMPRODUCT(1*(VALUE($C$29:$N$29)0)))),0)

in AQ11. This would only fail in the pretty unlikely situation that the monthly total is zero because the positive and negative amounts balance out excactly.

Reply | Quote

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.

Reply | Quote