Finding average value between percentiles

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

I can't see your data layout, so I can't help.

A few clarifications:

1) Your "working" formula that your text suggests is computing the average of the top quintile appears to be computing the average of the bottom 4 quintiles (everything but the top quintile). Your description of the non-working formula suggests that you are trying to find the average of each quintile, but the formula appears to be trying to get the average of everything but the 3rd quintile. It is not clear to me which you are trying to do (though I would assume the text is correct and that the formulas are in error).

2) Is there a reason you are avoiding the AVERAGEIFS() function (for backwards compatibility to pre2003 perhaps)? I think the AVERAGEIFS() function would be easier if you are allowed to use it (https://support.office.com/en-us/art...8-f7c5c3001690 ).

Assuming that your text description is the accurate description (you are trying to find the average of each quintile, I would proceed like this:

1) Enter 0, 0.2, 0.4, etc. in a suitable column (if DO3:DO8 is available, maybe there).
2) Enter a formula like =AVERAGEIFS($DN$3:$DN$150,$DN$3:$DN$15,">="&PERCENTILE($DN$3:$DN$150,DO3),$DN$3:$DN$150,"<"&PERCENTILE($DN$3:$DN$150,DO4)) a cell adjacent to the percentiles (maybe DP3). Note the mix of relative and absolute references.
2a) If you really don't like having the percentiles in DO, you can hard code those values into each copy of the formula and omit step 1.
2b) The same thing can be accomplished using your AVERAGE(IF(...)) structure, you just need to think through the IF(...) logic better. Let us know if this is necessary.
3) Copy DP3 and paste in DP4:DP7
4) Adjust the formula in DP7 so that it includes the max value in the average (adjust the final criteria so it is "<=" instead of just "<").

Is that helpful at all?

If DN contains errors, that suggests that DN contains a formula, in which case, I would probably deal with the errors in DN in DN (probably using the IFERROR() function https://support.office.com/en-us/art...6-63f3e417f611 ). Something like =IFERROR(current formula in DN,"#"). If you take care of the errors in DN, then your AVERAGEIFS() formula does not need to deal with the errors (and I am not sure how to add a "include in average if not error" criteria to AVERAGEIFS())

You also mention that you have 0 values in DN, which neither my formula handles nor do your formulas. This can be handled by the AVERAGEIFS() function by including a third criteria_range and criteria argument where the criteria is "<>0". Or, you could also handle this error in DN. IFERROR(IF(current formula in DN=0,current formula in DN,"#"),"#"). Note that this means computing the current formula in DN twice, which could become a performance concern if the current formula in DN is "slow" (like a "linear" exact match lookup function).