vendredi 9 octobre 2020

Excel: Mean of some cells in range if their two headers are equal to a value

I have a table that looks like that over hundreds of columns:

    +-------+------------+------------+------------+------------+------------+------------+------------+------------+
|       | 2020-10-10 | 2020-10-10 | 2020-10-09 | 2020-10-09 | 2020-10-03 | 2020-10-03 | 2020-10-02 | 2020-10-02 |
+-------+------------+------------+------------+------------+------------+------------+------------+------------+
|       | Sales      | Profits    | Sales      | Profits    | Sales      | Profits    |    Sales   | Profits    |
+-------+------------+------------+------------+------------+------------+------------+------------+------------+
| 06:00 | 23,00 $    | 14,95 $    | 92,00 $    | 59,80 $    | 31,00 $    | 20,15 $    |   67,00 $  | 43,55 $    |
+-------+------------+------------+------------+------------+------------+------------+------------+------------+
| 07:00 | 36,00 $    | 23,40 $    | 53,00 $    | 34,45 $    | 61,00 $    | 39,65 $    |   26,00 $  | 16,90 $    |
+-------+------------+------------+------------+------------+------------+------------+------------+------------+
| 08:00 | 35,00 $    | 22,75 $    | 84,00 $    | 54,60 $    | 68,00 $    | 44,20 $    |   86,00 $  | 55,90 $    |
+-------+------------+------------+------------+------------+------------+------------+------------+------------+ 

How can I have a formula that will look if the Weekday=Friday (for example) and the header is Sales? The formula that I want to make will get the mean of each of the values where the weekday and header is the same?

I tried some array AND function, MEANIF function and some other weird Index/match type of thing but they all failed.

Anyway to do this in Vanilla Excel and not VBA?

Thank you so much.

The results in a dashboard would read like this:

Avg. Mondays Sales: xx,xx$

Avg. Mondays Profits: xx,xx$

Etc.

Aucun commentaire:

Enregistrer un commentaire