I have two questions.
In a spread sheet, I have columns
A = date
B = product (eg. banana, apple, orange....in mixed order)
C = unit cost (can vary for same product on different date)
D = quantity
E = total cost
cell G1 = I can manually enter a specified date
Then, on the side, I'm trying to make a small summary chart that will update as more data continue to add to above chart. From googling, I pieced together below to show average cost up to now for selected product, eg. banana so far,
=SUMIFS(E1:E100,B1:B100,"banana")/SUMIFS(D1:D100,B1:B100,"banana")
Question 1: Is there simpler way to write this? eg. pull out the common condition "if product is banana", so this common condition doesn't need to re-appear in both top and bottom? Or maybe a whole different simpler way to write as one equation instead of one equation divide by another?
Question 2: Building on first question, is there simpler way if I want to look at average cost up to a specified date (ie. cell G1)?
=SUMIFS(C1:C100,B1:B100,"banana",A1:A100,"<="&G1)/SUMIFS(D1:D100,B1:B100,"banana",A1:A100,"<="&G1)
Above gets a whole lot more redundant and messier if I want to look at average cost of selected product in a date range from G1 to G2, for example, adding conditions A1:A100,">="&G1, A1:A100,"<="&G2
Because I pieced above from googling, I don't know if I'm writing more quotations and brackets than I need to. Any advice to shorten and simplify the equation is appreciated.
ps. I'm using Excel 2007
Thank you
In a spread sheet, I have columns
A = date
B = product (eg. banana, apple, orange....in mixed order)
C = unit cost (can vary for same product on different date)
D = quantity
E = total cost
cell G1 = I can manually enter a specified date
Then, on the side, I'm trying to make a small summary chart that will update as more data continue to add to above chart. From googling, I pieced together below to show average cost up to now for selected product, eg. banana so far,
=SUMIFS(E1:E100,B1:B100,"banana")/SUMIFS(D1:D100,B1:B100,"banana")
Question 1: Is there simpler way to write this? eg. pull out the common condition "if product is banana", so this common condition doesn't need to re-appear in both top and bottom? Or maybe a whole different simpler way to write as one equation instead of one equation divide by another?
Question 2: Building on first question, is there simpler way if I want to look at average cost up to a specified date (ie. cell G1)?
=SUMIFS(C1:C100,B1:B100,"banana",A1:A100,"<="&G1)/SUMIFS(D1:D100,B1:B100,"banana",A1:A100,"<="&G1)
Above gets a whole lot more redundant and messier if I want to look at average cost of selected product in a date range from G1 to G2, for example, adding conditions A1:A100,">="&G1, A1:A100,"<="&G2
Because I pieced above from googling, I don't know if I'm writing more quotations and brackets than I need to. Any advice to shorten and simplify the equation is appreciated.
ps. I'm using Excel 2007
Thank you