Calculating YTD of Monthly Averages

Quick question:

I've currently developed a data base with monthly sales to calculate YTD depending on a specific date chosen from a dropdown menu.

Formula is as follows:

Formula:

Please Login or Register  to view this content.

Where $L$1 is a droplist from which you can choose a date
$BH$3:$DD$3 is where the months are referenced
And $BH$4:$DD$4 is where the monthly sales are available

Now I would like to apply the same principle except divide each month by a number that is also available monthly in order to get the YTD of that ratio.

I know it can be easily done by simply recalculating monthly averages (i.e. Monthly Sales / Number of that same month) and then adding them up with the same formula as above but i'd really like to avoid doing that.

Any ideas?

Here you go.

Thanks for your help!!!

The method is correct, and I was tempted to do that, but because i'm handling a huge database I was trying to find a way to avoid having to calculate the ratios each month (=IFERROR(B4/AA4,0)) and having it directly integrated in the formula (if you see what i mean...)

So the formula would directly return the '3505.66' but without going through *($AA$5:$AX$5) but rather directly using ($AA$4:$AX$4)

I don't know whether that's possible but ideally, this is the approach I'd like to have.

Any further ideas?

I understand where you're coming from, and I was tempted to do that but to set you in the context. There more than 10,000 rows and column of dates that range over four years, each with their own monthly sales and teams. Your suggestion would have me create an array over the four years where the ratio would be calculated. Ideally, avoiding congesting the table with further data would be ideal.

Let me know if an idea pops into your head!

Thanks a lot!

That was what i was looking for!

u may try this one

=SUMPRODUCT(IFERROR(($B$2:$Y$2<=$B$8)*(B4:Y4)/($AA$4:$AX$4),""))