Hi All,

Please can someone help with the following formula which returns #VALUE!?

I want the formula to take all the figures from column N providing the corresponding row in column D has the word Red, furthermore the figures then need reducing by removing the highest 20% & the lowest 20% before taking the average of the remaining 60%.

=AVERAGE(IF($D2:$D5000,"Red",(IF($N2:$N5000<=PERCENTILE($N2:$N5000,0.2),FALSE,IF($N2:$N5000>=PERCENTILE($N2:$N5000,0.8),FALSE,$N2:$N5000)))))
Really appreciate the help
Thanks John