CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
GtwoK
GtwoK

Reputation: 591

Calculating averages using AVERAGEIF , when the values in the range are inserted via formula?

I have some data I am trying to calculate averages of. Each "item" in the data set is stored in a different row, with the "type" of item being stored in column B. For ex.

enter image description here

Here also is a sample sheet I've created to demonstrate

Each column contains a different stat for that item, which I am trying to average in a summary section of the sheet.

The formula =AVERAGEIF(B1:B, "Shirt", L1:L) works well to calculate these averages when the value of the cells is a number directly inserted in the cell, but for some of these cells, the value is calculated via a formula. AVERAGEIF doesnt seem to play well with these cells, as it always gives me a #DIV/0 error. The formulas are things like

=SWITCH(C1, "Cap", "$50", "Sweatshirt", "$115","Shirt", "$175", "", "") (which auto fills the sale price column based on the "type" listed in the C column, or

=IF(NOT(ISBLANK(N1)),IFERROR(O1/E1, ""),"") (which calculates a rate by dividing my profit column by the hours column depending on if column N has a value)

Now, the results of each of these formulas IS a number to anyone reading the sheet, but Sheets doesnt see it as that when trying to use AVERAGEIF. How can I tell it to convert the values to a number before trying to average them? Or is there a better way to calculate averages that would work here?

Upvotes: 0

Views: 146

Answers (1)

rockinfreakshow
rockinfreakshow

Reputation: 30281

The issue seems to be not because the values are inserted via formula but the way/style you are using them inside switch(). "$50" OR "$175" are text-based. just use them directly as numbers as such:

=SWITCH(C1, "Cap", 50, "Sweatshirt", 115,"Shirt", 175, "", "")

and then use Format > Number > Currency to give them the $ look for that column

Upvotes: 0

Related Questions