average ifs within a targeted range

Hi,
i want to do an average of the year-over-year change, in all these four columns: revenue, OI, ebitda, EPS that is Y/Y . But i only want to include in average if the answer falls in the range that is above -75% or below -75%. So if you look at the example , those in yellow would be excluded.

I tried to write a formula but it didn't work - i was trying to say like if you do the % change for the average only do it or when the answer is above -75% or below 75%.


=AVERAGEIFS((D3:D6/I3:I6)-1, N3:I6, ">="&-0.75, I36:I6, "<="&0.75)
ADDED ANOTHER FILE - MORE EXAMPLE

As there were no expected answers, this is a guess:

=BYCOL(L3:O6,LAMBDA(z,AVERAGE(FILTER(z,ABS(z)<0.75))))

1. Restore the original formulae in all of the data cells in columns W:Y.

2. Use:

=BYCOL(W4:Y52,LAMBDA(z,AVERAGE(FILTER(z,ABS(N(z))<0.75))))

Away from PC... in bright sunshine on my phone so excuse any bad spelling errors!!

BYCOL is one of a number of functions which support LAMBDA.

It is used when a 2D array is being calculated to return a one dimensional horizontal array... a row, in effect.

The calculation is performed column-by- column, each coulumn returning a SINGLE value.

BYROW, in contrast wouldvprocess the array row-by row and produce a singble column vertical array (ie a column).

You're welcome. Thanks for letting us know that you got an answer.

---

Please take a moment and consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution here today.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

- Thank you so very much!
- May I ask two more questions?
- Does this formula exclude those under -75% and those over +75%? Or is it just excluding those over 75%?
- Again when I tried to take this formula to another spreadsheet I got a SPILL error. See U57 in "another example added to the top".

1. Yes. Both are excluded. That's what the ABS is doing.

2. SPILL errors are caused when other stuff is in cells that the formula needs to place results. In the case of your latest sheet... MOVE the formula to an empty area, or delete your formulae that are in the way.

Woo Hoo!

You're welcome...