I am doing a test for a data analytics bootcamp. i have to use google sheets and i am an absolute beginner. We have a dataset called products that we have to extract on a google spreadsheet first. I will upload the link to my google spreadsheet here:
https://docs.google.com/spreadsheets/d/1m67VmLZispyTwFTmPdppsdJNtbvnZsZK2LBCSchUWmU/edit?usp=sharing
the question is to use a formula to say what the average unit price of all products listed is.
My formula was to write under colum F(unit price) the formula: =AVERAGE(F2:F78). but the number i get is 44702 which cant be correct, if you look at the table. also i dont know if i have to consider Column E, where the quantityperunit is stated to answer the question.
Could someone please help me?
CodePudding user response：
I feel like I answered this for you in another post recently. But I can add more details.
You are working with a spreadsheet that seems to have been created in the United States, yet you've set the locale to Germany. This is creating a conflict, because the decimal separator in Germany is a comma while the decimal separator in the US is a period.
In addition, your Col-F values are all strings, not numbers — except for the one entry in F66. So you have mixed data types, and math functions can't act on that mix.
This leaves the only valid number being 21.05. However, as I said, in Germany, this is not a valid number. It's a date: May 21. And the data of May 21 (2022) is 44702 days since the Google Sheets origin date of December 30, 1899 (which is how Sheets stores all dates). So that is why you are getting that result.
If you change your locale to "United States," this will solve some of your issues. But no matter what your locale, it won't change those strings in Col F to real numbers. So you'll need to convert them in place, or your formula is going to need to account for them.
Seeing as this is an assignment, it's unclear whether the instructor intended for you to have to address these problems, or whether the additional problems are an unintentional result of spreadsheet conversions that happened somewhere.
In addition to all of that, you interpreted the original question in your post rather than sharing the actual exact wording of the question; and wording matters.
First, I would ask the instructor if the numbers in Col F were all supposed to be real numbers, or if most of them being strings is part of the assignment.
In any case, given exactly what you have right now, this should produce the average unit price (regardless of quantity in stock, as we don't know the actual wording of that question):
=ArrayFormula(ROUND(AVERAGE(VALUE(SUBSTITUTE(F2:F78&"",".",","))),2))
It's a lot longer than it would have needed to be if not for the multiple factors I described above. That is, given the locale setting of Germany and the fact that you have mixed data types, all of the Col-F values had to be converted to strings, then all of the periods had to be SUBSTITUTEd with commas, then they all had to be converted to numbers with VALUE before AVERAGE could be applied.