Hello.

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 middlewhat 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 I33So, 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!

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.

=AVERAGEIFS(B33:B39,D33:D39,">"&0,7*I33,D33:D39,"<"&1,3*I33)

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.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History