Forum Discussion
Dynamic array function error(#N/A) when file moved to excel 2016.
Dear all,
Kindly I receive an error when moving file from 365 to 2016 i know it is not supported all i look for is a replacement to the formula i have written i have range of cells from $E$8:$E$382 and I want to apply bell curve on it as 2% of this range receive A grade and next 14% receive B and next 68% of the same range receive C and next 14% receive D and last 2% receive E any help ??
The problem might be with the OFFSET's height argument. That arrangement is a bit unusual. It might help if you're able to post a sample of the data.
The OFFSET doesn't work in 2016 because the height of the OFFSET is >1 causing a spill. In 2016, you'd need to wrap another function around the OFFSET to reduce it to a scalar (single value) so it won't produce an error.
A workaround for 2016:
=INDEX(names,ROWS(names)-6+(ROW(A1)-1))
So what is the formula that fails?
- =OFFSET('PDR Evaluation'!$E$8,(ROUNDDOWN(COUNTA('PDR Evaluation'!$E$8:$E$382)*2%,0)+ROUND(COUNTA('PDR Evaluation'!$E$8:$E$382)*14%,0)+ROUND(COUNTA('PDR Evaluation'!$E$8:$E$382)*68%,0)+ROUND(COUNTA('PDR Evaluation'!$E$8:$E$382)*14%,0)),0,ROUNDDOWN(COUNTA('PDR Evaluation'!$E$8:$E$382)*2%,0))
I don't see why this formula would fail in Excel 2016, but I have to admit it doesn't make much sense to me either...
