Turn Numbers Into Positive/Negative then Average

Hi guys, would you know how to crack this one up?

I basically look to achieve the outcome:

--- IF value in O3 = "y", then return number in N3 as positive
--- IF value in O3 = "n", then return number in N3 as negative

--- do the same for all found in the same column
--- AVERAGE and return one total number


The issue is that those "y", "n" in column O and numbers in column N are going 2,000 cells down with blank spaces and text in-between. So manual calculation as in attached file example... well, it's forever job and I don't know of any other way...

Would you be okay taking a look and suggest a better formula, to be entered in D14, please? Thanking in advance!

As per screenshot and file attached.


https://www.excelforum.com/attachmen...1&d=1614612602

https://www.excelforum.com/attachmen...1&d=1614612654

another alternative, based on your sample, would be to use SUMIF / COUNT:

=SUM(SUMIF(O:O,{"y","n"},N:N)*{1,-1})/COUNT(N:N)
confirmed with Enter

Thank you so much Glenn! Greatly appreciate you looking into it.

My file is already showing strong signs of getting slow... And because I will need to be sharing it with others, I need to avoid using anything that could then result in causing the issues for others. So no macros for sure and I'd very much want to avoid using arrays as well... is there no way without arrays? Like no way at all?...

Sorry I didn't mention it earlier, I didn't think that we would run into the arrays here!

Ah, that's the one, XLent, thanks a ton!! You guys are genius :-) Thank you!!!!