Common factor in formula divide by formula

[kancolle]

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

 

[jasonb75]

For what you are trying to do the way that you have written your formulas is the best way. No extra quotations / brackets in any of them that I can see.

For question 1 there is a way with sumproduct that may or may not work, but it would be less efficient if it did. Your existing formula, although longer, is the better option.

 

[kancolle]

Thank you for your clarification.

Because I'm self-learning from googling, I'm unsure what I piece together is optimized or not. When copy formula from product A to product B, need to correct redundant reference cells and common factors quite a few times. So, leading to the idea to search if excel can let us write formulas more logically and efficiently like real-life math.

I'll mark this thread solved, but if later anyone comes across any ideas or other version excel that may have new function that allows us to write formula more intuitively, please feel free to comment

 

[jasonb75]

When copy formula from product A to product B, need to correct redundant reference cells and common factors quite a few times.

It sounds like you need to use absolute references in the formula.
$A$1 Absolute row and column, when you copy the formula to another cell this will stay the same.
$A1 Relative row, absolute column. When you copy the formula down, the row will change. When you copy it right, the column will stay the same.
A$1 Absolute row, relative column. When you copy the formula down, the row will stay the same. When you copy it right the column will change.
A1 Relative row and column. When you copy the formula down, the row will change. When you copy it right the column will change.

=SUMIFS($C$1:$C$100,$B$1:$B$100,"banana",$A$1:$A$100,"<="&$G$1)/SUMIFS($D$1:$D$100,$B$1:$B$100,"banana",$A$1:$A$100,"<="&$G$1)

With regard to writing the formula more intuitively, the only thing that you would be able to do is name parts of the formula using the LET() function (office 365 only). There is no efficient single function that will do as needed.

 

[kancolle]

I see. Thank you