i have a problem.
i have alot of data. the data consists of each item in each other over a period of time.
so the data looks something like this:
- customer1, ordernr, item1, amount
- customer1, ordernr, item2, amount
- customer2, ordernr, item1, amount
- customer2, ordernr, item2, amount
ofc not each customer orders all the products every time. so i created a pivot table, with the customerNr on the left, below that the ordernr and then the items from left to right, and their respective amounts
now when i close all the dropsdowns, i see the customer number, and which item was ordered in total
what i need todo is simple calculate the average of each item over the data time
in theory this would work with the average function when i click on the result and it does. but the problem since some cells are blank (cuz the item wasnt ordered), that the average is wrong, since the average is only calculating 5 out of 6 times, so amount/5 instead of amount/6
i have done a good bit of googling on how to solve this, but i cant find a way other than creating multiple sheets to transform the data how i need it, but thats too large for what i am trying todo.
any idea if theres an easier way ?
