Search code examples
formulaaveragespreadsheetlibreofficelibreoffice-calc

How to calculate average annual salary in libreoffice calc

I have salary data table from 10 years period. Every column has properly set data type (date for "B", number for "C" and "E".

sample data table

I'm trying to write a formula to calculate average salary for every year. In column "E" I've manually entered all possible years and in column "F" should be an yearly average, according to year from "E".

So, my best try is this formula: =AVERAGEIF(YEAR(B2:B133);"="&E2;C2:C133)

Trying so calculate an average from column C, where year in date from column B equals a year in column E

averageif formula

But all I get is an error Err:504. Figured out, that problem is in YEAR(interval) part, but can't get what exactly...

Can someone point that out?

Thank you!

Solution

  • There are actually many possibilities to solve this.

    • @JvdV answer;
    • using an array formula with @JvdV solution;
    • using an array formula with a combination of AVERAGE() and IF();
    • using the SUMPRODUCT() function;
    • and surely many other solutions that I don't know about!

    Please beware: I use , instead of ; as formula separator, according to my locale; adapt to your needs.

    A side note on "array formulas"

    This kind of formulas are applied by mandatory pressing the Ctrl + Shift + Enter key combination to insert them, not only Enter or Tab or mouse-clicking elsewhere on the sheet.
    The resulting formula is shown between brackets {}, which are not inserted by the user but are automatically shown by the software to inform that this is actually an array formula.
    More on array formulas i.e. on the LibreOffice help system.

    Usually you cannot drag and drop array formulas, you have to copy-paste them instead.

    Array formula with @JvdV solution

    The solution of JvdV could be slighly modified like this, and then inserted as an array formula:

    =AVERAGEIFS(C$2:C$133,YEAR($B$2:$B$133),"="&E2)

    When you insert this formula with the Ctrl + Shift + Enter key combination, the software puts the formula into brackets, so that you see it like this: {=AVERAGEIFS(C$2:C$133,YEAR($B$2:$B$133),"="&E2)}

    You cannot simply drag the formula down, but you can copy-paste it.

    Array formula with a combination of AVERAGE() and IF():

    For your example, put this formula in cell F2 (for the year 2010):

    =AVERAGE(IF(YEAR($B$2:$B$133)=E2,$C$2:$C$133))

    When you insert this formula with the Ctrl + Shift + Enter key combination, the software puts the formula into brackets, so that you see it like this {=AVERAGE(IF(YEAR($B$2:$B$133)=E2,$C$2:$C$133))}

    You cannot simply drag the formula down, but you can copy-paste it.

    SUMPRODUCT() formula:

    My loved one...
    Plenty of resources on the web to explain this formula.
    In your situation, this would give:

    =SUMPRODUCT($C$2:$C$133,--(YEAR($B$2:$B$133)=E2))/SUMPRODUCT(--(YEAR($B$2:$B$133)=E2))

    This one you can drag down to your needs.