Google Spreadsheet Post #852
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Nov 05, 2012
user Rayner Lim said : (http://productforums.google.com/forum/?zx=xdgf04nj8c5p#!category-topic/docs/spreadsheets/ztakMQxmB0Q)
Array Formula Row by Row Average and Minimum
Hi :)
This is my sample spreadsheet (feel free to edit it if you can help me) and here is a screenshot of it:
As you can see, I created this spreadsheet to compare prices of different parts across multiple shops, and most importantly to find the average and lowest price for each individual part.
This would be easy to achieve by just using the AVERAGE and MIN formulas, if not for the fact that the formula is not continued if a new row is inserted.
However, this is essential as I need this spreadsheet to be editable by anyone, such that when he or she adds a new part with the corresponding prices in list view (which inserts a new row) the average and lowest price is automatically calculated.
In cell G1 for the average price, I used the formula
which did the job, as can be seen in row 11 which was added in list view as a new row.
=ARRAYFORMULA(IF(ROW(A:F)=1,"
However, I cannot come up with any formula for the lowest price, as the MIN formula uses the entire range of values, but I need the lowest price row by row.
Can anyone help me with this? I've been searching round the web for this but there are still no results.
If you can come up with a simpler or more elegant formula for the average price, i would also appreciate it.
Thanks a lot in advance if you can help me!
---------------------------------------------------------------------------------------------
since the user already has computed the AVERAGE values row by row, in the following I present a solution for row by row Minimum prices
No comments:
Post a Comment