Solved Excel: i want to program an average around a changeable peak

Microsoft Excel 2010
September 10, 2012 at 07:27:30
Specs: Windows 7


I am new in programming with excel, and so i struggle with programing cells.

The Case:

I have different pumps which provided different performances when tested.
and now i have loads of data to analyse.
f.e. i have the columns "efficiency"(D33:D39) and "flow" (B33:B39)
"flow" has its peak at the last entry
"efficiency" has its peak somewhere in the middle

what i need is:

find the maximum value(BEP) in "efficiency" and take an average over all entries in the range of 0,7*Flow(at BEP) to 1,3*Flow(at BEP)

what i tried:
=AVERAGEIF(D33:D39;"< 1,3*I33 AND >0,7*I33";B33:B39)

I33 =MAX(D33:D39)

It didnt work because the criteria didnt have the right format.
he neither accepted "< (1,3*60)" nor " < (I33*1,3)"
he just accepted "<80" or I33

So, how can i do such a complexe ;) calculation on excel? respectively. how can i adjust the cirteria so Excel finds the requested peak and its range itself?

Thank you for help!

See More: Excel: i want to program an average around a changeable peak

Report •

September 10, 2012 at 08:24:57
✔ Best Answer
Since you have more than one criteria, try AVERAGEIFS (plural) instead of AVERAGEIF (singular).

You also have to do the calculations (0,7*I33, etc.) outside of the quotes or Excel won't evaluate them.


Note that the order of the arguments is a little different than with AVERAGEIF.

=AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2,…)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question