SUMPRODUCT, or something else entirely?

[Guest]

I have cells B3 through M3 - these hold MTD totals. I have cells B7 through
M7 - these hold MTD percentages. I have cell N3 which is the total for the
months [ SUM(B3:M3) ]. We have been using the below formula to calculate the
YTD percentage, but it doesn't feel right. Any suggestions?

=IF(ISERROR(SUMPRODUCT(B7:M7,B3:M3)/N3),"",SUMPRODUCT(B7:M7,B3:M3)/N3)

Again, I only want YTD percentages.
Thanks,

 

[Bob Phillips]

Whys isn't the YTD figure just the current MTD figure? I c an't say I
understand your logic, but why does the formula feel wrong?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Hi Bob,

I think the YTD should be a reflection of the MTD, but it is not the same as
the month to date. I would like to think the YTD to be more like an average,
which the YTD currently does not show. Perhaps I just don't fully comprehend
the SUMPRODUCT, but it seems to me that it would not be required to calculate
what is truly a running number. The feeling I have is supported by just
taking all the YTD totals and dividing by the number of months, which does
not equal what we show as the YTD. Additionally, why would we ever want to
use the totals (cell N3) in the calculation when we only want to know the YTD
percentage?

Just not getting it.

 

[Bob Phillips]

The difficulty I have is that YTD would be all months totals so far. MTD, if
it exists at all, would be all days totals so far in this month, but you
don't seem to be doing that.

The true running number could be gotten with

=SUM($B$3:B3)

and just copy across. Excel would update this to =SUM($B$3:C3) in column C,
etc.

Then what would a YTD percentage be? A month percentage would (could?) be
any month as a percentage of the YTD figures, which means that Jan would be
100% in Jan, but maybe only 40% in Feb .

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Hi Bob,

I think the YTD should be a reflection of the MTD, but it is not the same as
the month to date. I would like to think the YTD to be more like an average,
which the YTD currently does not show. Perhaps I just don't fully comprehend
the SUMPRODUCT, but it seems to me that it would not be required to calculate
what is truly a running number. The feeling I have is supported by just
taking all the YTD totals and dividing by the number of months, which does
not equal what we show as the YTD. Additionally, why would we ever want to
use the totals (cell N3) in the calculation when we only want to know the YTD
percentage?

Just not getting it.

I have cells B3 through M3 - these hold MTD totals. I have cells B7 through
M7 - these hold MTD percentages. I have cell N3 which is the total for the
months [ SUM(B3:M3) ]. We have been using the below formula to calculate the
YTD percentage, but it doesn't feel right. Any suggestions?

=IF(ISERROR(SUMPRODUCT(B7:M7,B3:M3)/N3),"",SUMPRODUCT(B7:M7,B3:M3)/N3)

Again, I only want YTD percentages.
Thanks,

 

[DCSwearingen]

I do a lot of Key Indicators requiring Daily, WTD, MTD, & YTD
percentages.

You have to be careful of averaging averages and percents. This would
be OK if all had equal weighting, but not if they are differing in
magnitude, particularly the magnitude of individual divisors.

Example, we have a seasonal production year in the food industry. The
month of July may only have 10 Million pound produced and have 1
Million pounds Held, a Held Rate of 10%, while the remaining months may
all have 30 Million pound produced an also have 1 Million pounds held in
each of the months, or 3.3% Held Rate for each of those months.

3.3% times 11 months = 36.7%; Add the 10% for July gives 46.7%.
Divide by 12 months gives a Year End (YTD after 12 Mos) percentage of
3.9%.

But, if I take the actual YTD pound held (12 Million) and divide by the
actual YTD production (340 Million), I get 3.5% Held Rate.

Can you explain what your MTD and YTD percents are calculated from?

 

[Guest]

Heya,

the MTD is comprised of daily reports that show each day's service levels,
or how well we are doing. The YTD is comprised of the monthly MTD service
levels. Does this make sense?

thanks,

 

[redneck joe]

I'm not really good with excel formulas yet, but I do know the below is
true. Do not average averages and percents - use the acual numbers..

I do a lot of Key Indicators requiring Daily, WTD, MTD, & YTD
percentages.

You have to be careful of averaging averages and percents. This would
be OK if all had equal weighting, but not if they are differing in
magnitude, particularly the magnitude of individual divisors.

 

[Bob Phillips]

Can you post an example workbook, with some sample results somewhere?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Heya,

the MTD is comprised of daily reports that show each day's service levels,
or how well we are doing. The YTD is comprised of the monthly MTD service
levels. Does this make sense?

thanks,