COUNTIFS function help please

[andrewmurray86]

Hi all,

This is my first post here:

I'm attempting to identify the smallest number within a column. I have a series of sheets with a column that's filled with profit from forex trades.
The formula is here

=MIN(IF(O4:O254<0,MIN(O4:O254),O4:O254))

It is working fine to give the largest loss

I have an overview sheet also which has more data in the columns and I need to exclude some figures from the calculation, they are giving skewed results

=COUNTIFS(F2:F252,"isnumber", N2:N252, MIN(IF(N2:N252<0,MIN(N2:N252),N2:N252)))

Please note that the N/O column difference is only due to the way the information is displayed, the

Essentially I am trying to exclude the any rows from the calculation if the cell in F is blank.

The current formula returns a $0.00 value but that's not correct.

Any assistance would be wonderful and very appreciated.

 

[andrewmurray86]

I got a Value error on the Clean formula beyond the cells with values, I impleneted this if error to give zero
=IFERROR((CLEAN(N3))*1,0)
But still got the -100 figure. Clearly this spreadsheet is being super difficult

 

[andrewmurray86]

I have other formulas I need help with but they also need to exclude the same values so it may not be helpful to get them working if the sheet can't exclude the values??

 

[andrewmurray86]

OK, I got it, I entered as an array Ctrl+Shift+Enter and it worked

Thank you so much.

 

[kweaver]

CLEAN doesn't handle two other CHARs. You might try:

 =SUBSTITUTE(N2,CHAR(127),””)

and

  =TRIM(SUBSTITUTE(N2,CHAR(160),” “))

and see if that makes any difference. CHAR(160) is a non-breaking space and is used often on the web.
Make sure you get the straight quotes in that formula.

 

[andrewmurray86]

I'm now trying to get the average loss value correct with this formula
=AVERAGEIF(N2:N252,(IF(C2:C252<>"balance",(N2:N252<0))))
Essentially to exclude the same data as before but I'm getting a Div/0 error whether I enter as a formula or array?

 

[andrewmurray86]

If I use this formula I get an average with the values that should be excluded, but aren't.
=AVERAGEIFS(N2:N252,(IF(C2:C252<>"balance",N2:N252,"")),"<0")