Hi All, I have attached a sample workbook.
Summary Tab
E:6 to G:9 is using a sumifs formula on the "Data" tab to total my "actuals"
H:6 to P:9 is my forecast which would be calculated as the sum of Actuals E through G/12-Cell D3
Is it possible to combined these 2 formulas such that if the sumifs returns a total use that total, otherwise do the forecast calculation? Is this possible with a regular formula or maybe in vba?
Thanks!
Some manually calculated expected forecast results would have been a help, but try:
=IF(E$5<=$D$3,SUMIFS(Data!$C$2:$C$13,Data!$A$2:$A$13,Summary!$C6,Data!$D$2:$D$13,Summary!E$5),SUM($D6:INDEX($D6:D6,$D$3+1))/(12-$D$3))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Oh. And take a moment to update your profile. Clearly, you are NO LONGER using Excel 2003 (.xlsx file attached). Providing solutuons for long since upgraded versions can hinder getting effective solutions
Hi,
please fill in manually the results that you are expecting to see in your forecast columns.
Thanks.
Thanks Digital Nomad, works perfect! And for the profile tip.
PS I call myself a Finance Nomad PA->TX->VT->FL->CA
Mmm. Spent one night in FL, en route to Ecuador, but not in any of the others... yet.
Glad to have helped and thanks for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks