I wish to apply the following formula to values in a spreadsheet, Col. BG [BG15:BG234] =SUMPRODUCT(SMALL(BGxxx:BGxxx,{1,2,3,4,5,6,7,8}))/8 where xxx are automatically applied. The formula should be applied to last 20 entries in Col. BG.
The entries are dynamic and are added from time to time in sequence, there are no unfilled cells in the column, the cells have a value or a dash when no value [the dash is required to satisfy another formula]. For clarity BG15 is the first entry & BG234 would be last. [This could be reversed].
So my question is; can a formula be written so that Excel automatically finds the last 20 value entries in Col. BG and applies formula or the same result by another means. I cannot use a reference coloumn or another sheet. The resulting value would be displayed in another cell.
The entries are dynamic and are added from time to time in sequence, there are no unfilled cells in the column, the cells have a value or a dash when no value [the dash is required to satisfy another formula]. For clarity BG15 is the first entry & BG234 would be last. [This could be reversed].
So my question is; can a formula be written so that Excel automatically finds the last 20 value entries in Col. BG and applies formula or the same result by another means. I cannot use a reference coloumn or another sheet. The resulting value would be displayed in another cell.