I have a formula that works in Excel 2007, but when I load it into Excel 2010, I get this error:
"Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated."
Here is the formula:
{=IF(SUM(E10:E61)<1,"N/A",AVERAGE(SMALL(INDIRECT("E"&LARGE(ISNUMBER(OFFSET(E10,0,0,MATCH(1000,E10:E61),1))*ROW(OFFSET(E10,0,0,MATCH(1000,E10:E61),1)),ROW(INDIRECT("1:"&MIN(COUNTA(E10:E61),20))))),ROW(INDIRECT("1:"&MIN(COUNTA(E10:E61),10))))))}
What it's doing is reading values in a column, which may have blank entries in between values and/or a variable number of entries, taking only the bottommost 20 values, then finding the smallest 10 of those, and averaging them for the result.
Does anyone have any ideas on how I can fix this?
Thanks!
Bookmarks