Sep 13 2022 05:57 AM
Hey gus!
I am trying to find the average cost for each category under specific criteria.
Inf_Sales_Data!B:B is the month and this should match the current month on the sheet.
Inf_Sales_Data!AB:AB is the different categories I am looking at that are listed on a table on my sheet from A89 onwards.
Inf_Sales_Data!T:T is the categories that include normal 1,2,3,4 and 5
Inf_Sales_Data!J:J is the cost that should be averaged.
See my formula below:
=AVERAGE(IFS((Inf_Sales_Data!B:B=$B$2;Inf_Sales_Data!AB:AB=A89;Inf_Sales_Data!T:T="normal";Inf_Sales_Data!J:J)+(Inf_Sales_Data!A:$A=$B$1;Inf_Sales_Data!B:B=$B$2;Inf_Sales_Data!AB:AB=A89;Inf_Sales_Data!T:T="normal 2";Inf_Sales_Data!J:J)+(Inf_Sales_Data!A:A=$B$1;Inf_Sales_Data!B:B=$B$2;Inf_Sales_Data!AB:AB=A89;Inf_Sales_Data!T:T="normal 3";Inf_Sales_Data!J:J)+(Inf_Sales_Data!A:A=$B$1;Inf_Sales_Data!B:B=$B$2;Inf_Sales_Data!AB:AB=A89;Inf_Sales_Data!T:T="short";Inf_Sales_Data!J:J)+(Inf_Sales_Data!A:A=$B$1;Inf_Sales_Data!B:B=$B$2;Inf_Sales_Data!AB:AB=A89;Inf_Sales_Data!T:T="normal 4";Inf_Sales_Data!J:J)+(Inf_Sales_Data!A:A=$B$1;Inf_Sales_Data!B:B=$B$2;Inf_Sales_Data!AB:AB=A89;Inf_Sales_Data!T:T="normal 5";Inf_Sales_Data!J:J)))
Not only does my formula return an error, but there must be a better way to do this!
I thought about listing the categories normal 1,2,3,4 and 5 in a list as {"nomal";"nomal 1";"nomal 2";"nomal 3";"nomal 4";"nomal 5"}.
(Also please note that I am using European settings on google sheets, therefore I use the ; instead of a comma)
I am open to any tips, thanks so much!
Sep 13 2022 06:06 AM
Perhaps
=AVERAGE(IF((Inf_Sales_Data!$A$2:$A$1000=$B$1)*(Inf_Sales_Data!$B$2:$B$1000=$B$2)*(Inf_Sales_Data!$AB$2:$AB$1000=A89)*((Inf_Sales_Data!$T$2:$T$1000="normal")+(Inf_Sales_Data!$T$2:$T$1000="normal 1")+(Inf_Sales_Data!$T$2:$T$1000="normal 2")+(Inf_Sales_Data!$T$2:$T$1000="normal 3")+(Inf_Sales_Data!$T$2:$T$1000="normal 4")+(Inf_Sales_Data!$T$2:$T$1000="normal 5"));Inf_Sales_Data!$J$2:$J$1000))
Sep 13 2022 06:14 AM
Sep 13 2022 06:19 AM
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.
Sep 13 2022 08:29 AM - edited Sep 13 2022 08:32 AM
I have taken over the creation of a sample file. At least as I have understood it.
The mean value is calculated with AVERAGEIFS. This function has been available since Excel 2007, so it should be suitable for everyone. It is also available in Google Sheets with the same syntax.