Getting annual average

Good day! I am working on a shipping company as a low level entry. Given the data in the attached spreadsheet, I need to get the average annualise tonnage per ship. Is this even possible? Can someone help me please.

Thanks for the response mehmetcik. I tried the formula and it gave me the same results as when I just used SUM. Am I doing it wrong?

M2=SUMPRODUCT((YEAR($B$2:$B$23)=$L2)*($G$2:$G$23))/SUMPRODUCT(--(YEAR($B$2:$B$23)=$L2))

copy down

N2=SUMPRODUCT((YEAR($B$2:$B$23)=$L2)*($E$2:$E$23))/SUMPRODUCT(--(YEAR(B$2:B$23)=$L2))

copy down

(Forgive me if this is a duplicate. I coulda sworn I posted it earlier. But I don't see it now.)

Originally Posted by joeu2004

I believe the average annualized tonnage per ship is:
=J2*SUM(G2:G23)/SUM(E2:E23)

Likewise, if you want the average annualized tonnage per ship for each year (not what you asked for), enter the following into O2 and copy down:

=$J$2*M2/N2

Originally Posted by joeu2004

(Forgive me if this is a duplicate. I coulda sworn I posted it earlier. But I don't see it now.)



Likewise, if you want the average annualized tonnage per ship for each year (not what you asked for), enter the following into O2 and copy down:

=$J$2*M2/N2

Thank you everyone who replied most especially, thanks for the explanation on how the the formula works joeu2004.

Please note I have no prior experience to complicated formulas in excel thus my username to be newbie. Today I was asked to add the year to date annualised rate. Added in column I is the unloading rate. I used the formula to get the year to date rate but the numbers seem to be too low. PLEASE PLEASE can you help me?

Originally Posted by newbie540

Today I was asked to add the year to date annualised rate. Added in column I is the unloading rate. I used the formula to get the year to date rate but the numbers seem to be too low.

Your questions have more to do with math than with Excel. And even more to do with the definition of terms, which is unclear.

First, how did you calculate column I?

I would expect =G2/E2 in I2. But that results in 142.053554228479, not 179.678362573099, which you have as a constant (copy-and-paste-value).

Second, the term "year-to-date" annualized rate is unclear. What year; and to what date?

Your calculation is even less clear. If I2 is tonnage per hour, then I2*E2 is tonnage. And I2*E2/L2 is tonnage per hour in a year. That is neither "year-to-date", not "annualized".

Off-hand, the only sense that I can make of "year-to-date annualized rate" is the formula that I provide in response #8, specifically for 2020.

That is, "year-to-date" usually means __current__ year to __current__ date (in the same year). So: (sum of tonnage for 2020) / (sum of unloading hours for 2020) times work-hours per year:

=$L2*O4/P4

If you do not understand the mathematical calculation that is being asked of you, I suggest that you get clarification by example.

It is impossible (or foolish) to write the Excel formula without understanding the math. GIGO!

Originally Posted by newbie540

we are supposed to be unloading at a minimum of 3 million tonnes/year and the main idea of getting the "year to date annualized rate" is to see whether we are on track on achieving that goal by end of 2020 or not

Good input! Based on that, I believe the formula that I provided in #10 does just that.

It is about 3.8M. But if it were under 3M, it tells you that you need to unload tonnage in fewer hours; that is, increase the crews or work more efficiently. Alternatively, increase the rate at which ships and tonnage arrives; presumably not under your control.

In contrast, the other formulas calculated tonnage per year. That might tell you that you need more tonnage per ships and/or more ships. Again, probably not under your control.

Finally, you can fine-tune your formulas, similar to the suggestion in mehmetcik's response #2 (improved), to wit:

O2: =SUMPRODUCT(--(YEAR($C$2:$C$23)=N2), $G$2:$G$23)
P2: =SUMPRODUCT(--(YEAR($C$2:$C$23)=N2), $E$2:$E$23)

Note the use of comma (","), not multiplication ("*").

Alternatively, you could write (YEAR($C$2:$C$23)=N2)*$G$2:$G$23. But there is no need to use both double-negate ("--") and multiplication ("*").

Originally Posted by joeu2004

Good input! Based on that, I believe the formula that I provided in #10 does just that.

It is about 3.8M. But if it were under 3M, it tells you that you need to unload tonnage in fewer hours; that is, increase the crews or work more efficiently. Alternatively, increase the rate at which ships and tonnage arrives; presumably not under your control.

In contrast, the other formulas calculated tonnage per year. That might tell you that you need more tonnage per ships and/or more ships. Again, probably not under your control.

Finally, you can fine-tune your formulas, similar to the suggestion in mehmetcik's response #2 (improved), to wit:

O2: =SUMPRODUCT(--(YEAR($C$2:$C$23)=N2), $G$2:$G$23)
P2: =SUMPRODUCT(--(YEAR($C$2:$C$23)=N2), $E$2:$E$23)

Note the use of comma (","), not multiplication ("*").

Alternatively, you could write (YEAR($C$2:$C$23)=N2)*$G$2:$G$23. But there is no need to use both double-negate ("--") and multiplication ("*").

joeu2004 thank you so so much for your patience with me. i truly appreciate your help. you just literally saved my job! THANK YOU!