andrewmurray86

New Member
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

New Member
Actually blank, there is no data in those cells
Interesintly I tried
=MIN(IF(F2:F25<>"",N2:N25,""))
limited range

And got a #value error

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

kweaver

Well-known Member
When I used the formula for rows 2 to 25, I got a min value of -40.64.
All this is suggesting to me is that there are extraneous characters in the F and/or the N column that came over from your download.

See what you get with:

Code:
``=MIN(IF(TRIM(F2:F252)<>"",N2:N252,""))``

Alternatively, as you suggested, this also gives -42.44

Code:
``=MIN(IF(C2:C252<>"balance",N2:N252,""))``

andrewmurray86

New Member
I'm still getting -100 in the target cell for the "balance" formula

and #Value! for the Trim formula

kweaver

Well-known Member
Well, as you can see, that's not what I get in U13

Book7
ABCDEFGHIJKLMNOPQRSTU
1
2213919162020.05.06 00:29:47sell0.03eurnzd-e1.791991.8143102020.05.06 00:31:351.79421400-4.031 - eurnzd-e3995.97
32.21E+082020.05.06 00:29:47sell0.03eurnzd-e1.791991.814311.773632020.05.06 00:31:341.79421400-4.046 - eurnzd-e3991.93Updated when trade is closed
42.21E+082020.05.06 00:38:04sell0.03eurnzd-e1.792231.8145402020.05.06 00:38:311.79424400-3.653 - eurnzd-e3988.28Realized Profit/Loss770536.2Profit/Loss of all closed trades
52.21E+082020.05.06 00:38:04sell0.03eurnzd-e1.792231.814541.773862020.05.06 00:38:301.79424400-3.644 - eurnzd-e3984.64Gross profit617.83Total profit of all won trades
62.21E+082020.05.06 00:38:46sell0.03gbpjpy-e132.515133.84702020.05.06 00:39:37132.666400-4.251 - gbpjpy-e3980.39Gross loss789.27Total loss of all lost trades
72.21E+082020.05.06 00:38:46sell0.03gbpjpy-e132.515133.847131.4792020.05.06 00:39:36132.666400-4.262 - gbpjpy-e3976.13Expected payoff-2.06554Average trade outcome
82.22E+082020.05.11 23:13:38buy0.05audjpy-e69.86169.00770.7152020.05.13 19:18:0969.007400-39.921 - audjpy-e3936.21Profit factor0.782787Gross profit / gross loss
142.22E+082020.05.15 23:31:25sell0.03gbpjpy-e129.765131.203128.3912020.05.18 18:23:07131.203400-40.393 - gbpjpy-e3798Average profit trade19.93
152.22E+082020.05.15 23:31:26sell0.03gbpjpy-e129.765131.20302020.05.18 18:23:07131.203400-40.394 - gbpjpy-e3757.61Average loss trade-15.18
162.22E+082020.05.15 23:40:17sell0.05nzdchf-e0.576280.584110.569152020.05.18 16:41:360.58412400-40.641 - nzdchf-e3716.97Long trades30
172.22E+082020.05.15 23:40:18sell0.05nzdchf-e0.576280.5841102020.05.18 16:41:360.58412400-40.642 - nzdchf-e3676.33Short trades22
Sheet2
Cell Formulas
RangeFormula
U13U13=MIN(IF(C2:C252<>"balance",N2:N252,""))

andrewmurray86

New Member

Shall I perhaps send the workbook? I'm really confused as to how this is happening.

kweaver

Well-known Member
You can't upload a workbook on the site because of concerns about a possible virus.

You might try something like this in the AA2 cell and fill down:

Code:
``=SUBSTITUTE(N2,CHAR(CODE(LEFT(N2))),"")``

and see if there's a 202 (leading character) somewhere.

Last edited:

andrewmurray86

New Member

That's understandable.
ABCDEFGHIJKLMNOPQRSTUVW
1TicketOpen TimeTypeSizeItemPriceS / LT / PClose TimePrice2CommissionTaxesSwapProfitTrade4000
22213919162020.05.06 00:29:47sell0.03eurnzd-e1.791991.814310.000002020.05.06 00:31:351.79421400-4.031 - eurnzd-e3995.97
32213919122020.05.06 00:29:47sell0.03eurnzd-e1.791991.814311.773632020.05.06 00:31:341.79421400-4.046 - eurnzd-e3991.93Updated when trade is closed
42213924122020.05.06 00:38:04sell0.03eurnzd-e1.792231.814540.000002020.05.06 00:38:311.79424400-3.653 - eurnzd-e3988.28Realized Profit/Loss770536.2Profit/Loss of all closed trades
52213924112020.05.06 00:38:04sell0.03eurnzd-e1.792231.814541.773862020.05.06 00:38:301.79424400-3.644 - eurnzd-e3984.64Gross profit617.83Total profit of all won trades
62213924182020.05.06 00:38:46sell0.03gbpjpy-e132.515133.8470.0002020.05.06 00:39:37132.666400-4.251 - gbpjpy-e3980.39Gross loss789.27Total loss of all lost trades
72213924172020.05.06 00:38:46sell0.03gbpjpy-e132.515133.847131.4792020.05.06 00:39:36132.666400-4.262 - gbpjpy-e3976.13Expected payoff-2.06554Average trade outcome
82216104502020.05.11 23:13:38buy0.05audjpy-e69.86169.00770.7152020.05.13 19:18:0969.007400-39.921 - audjpy-e3936.21Profit factor0.782787Gross profit / gross loss
142220254812020.05.15 23:31:25sell0.03gbpjpy-e129.765131.203128.3912020.05.18 18:23:07131.203400-40.393 - gbpjpy-e3798Average profit trade\$19.93
152220254822020.05.15 23:31:26sell0.03gbpjpy-e129.765131.2030.0002020.05.18 18:23:07131.203400-40.394 - gbpjpy-e3757.61Average loss trade-\$15.18
162220255382020.05.15 23:40:17sell0.05nzdchf-e0.576280.584110.569152020.05.18 16:41:360.58412400-40.641 - nzdchf-e3716.97Long trades30
172220255392020.05.15 23:40:18sell0.05nzdchf-e0.576280.584110.000002020.05.18 16:41:360.58412400-40.642 - nzdchf-e3676.33Short trades22
202221284802020.05.19 23:46:27buy0.03audjpy-e70.37868.9360.0002020.05.21 23:33:5270.6154006.573 - audjpy-e3681.66Max. consecutive wins6Longest winning streak (number of trades)
212221284792020.05.19 23:46:27buy0.03audjpy-e70.37768.93671.3402020.05.21 23:33:5170.6154006.614 - audjpy-e3688.27Max. consecutive losses1Longest losing streak (number of trades)
242221284912020.05.19 23:49:08buy0.03eurusd-e1.092501.080281.100652020.05.21 15:25:301.1006540024.451 - eurusd-e3812.48Paid commissions220
262221503252020.05.20 00:01:34balanceFinancing4-0.48 3812.3Win Rate37.35%#VALUE!
312221985372020.05.21 00:01:48balanceFinancing4-2.68 4007.91
362222375342020.05.22 00:01:27balanceFinancing-0.46 4053.31
372222769442020.05.23 00:01:31balanceFinancing-0.24 4053.07
382223258612020.05.26 00:01:34balanceFinancing-0.37 4052.7
392223760162020.05.27 00:01:31balanceFinancing-0.25 4052.45
422224250192020.05.28 00:01:51balanceFinancing-0.63 3982.09
452224519572020.05.28 23:32:39sell0.04usdchf-e0.964140.973330.958332020.06.01 05:53:210.9601040016.841 - usdchf-e4032.77
462224519582020.05.28 23:32:40sell0.04usdchf-e0.964140.973330.000002020.06.01 05:53:220.9601040016.832 - usdchf-e4049.6
472224739622020.05.29 00:02:04balanceFinancing-0.92 4048.68
482225254872020.05.30 00:01:40balanceFinancing-0.37 4048.31
512225786202020.06.02 00:01:32balanceFinancing-0.17 3928.14
562226095132020.06.02 23:50:46sell0.02euraud-e1.619271.644930.000002020.06.04 23:39:091.63130400-16.81 - euraud-e3960.17
572226095122020.06.02 23:50:46sell0.02euraud-e1.619271.644931.602732020.06.04 23:39:081.63138400-16.912 - euraud-e3943.26
582226095152020.06.02 23:51:16sell0.02eurnzd-e1.753681.745070.000002020.06.10 01:29:001.7450740011.145 - eurnzd-e3954.4
592226095142020.06.02 23:51:16sell0.02eurnzd-e1.753681.781261.736062020.06.05 15:32:141.7360640022.826 - eurnzd-e3977.22
642226313542020.06.03 00:01:53balanceFinancing-0.57 4039.76
672226821092020.06.04 00:01:42balanceFinancing4-1.65 4059.7
682227332392020.06.05 00:01:57balanceFinancing-0.58 4059.12
692227769002020.06.06 00:01:59balanceFinancing-0.25 4058.87
702228226272020.06.09 00:01:59balanceFinancing-0.26 4058.61
732228703232020.06.10 00:01:35balanceFinancing4-0.29 3973.47
742228952622020.06.10 22:29:42sell0.03usdchf-e0.942550.953120.000002020.06.12 19:56:190.95312400-33.443 - usdchf-e3940.03
752228952612020.06.10 22:29:42sell0.03usdchf-e0.942550.953120.935772020.06.12 19:56:190.95312400-33.444 - usdchf-e3906.59
772229171452020.06.11 00:01:32balanceFinancing-2.53 3836.06
782229634452020.06.12 00:01:37balanceFinancing-1.38 3834.68
792230073472020.06.13 00:01:51balanceFinancing-1.05 3833.63
802230583522020.06.16 00:01:50balanceFinancing-1.02 3832.61
812231049612020.06.17 00:01:55balanceBalance update-0.99 3831.62
822231080582020.06.17 00:01:58balanceFinancing-0.99 3830.63
832231088102020.06.17 00:44:15balanceBalance update0.99 3831.62
842231537272020.06.18 00:02:00balanceFinancing-3.06 3828.56
85 3828.56
86 3828.56
87 3828.56
88 3828.56
89 3828.56
90 3828.56
91 3828.56
92 3828.56
93 3828.56
94 3828.56
95 3828.56
96 3828.56
97 3828.56
98 3828.56
99 3828.56
100 3828.56
101 3828.56
102 3828.56
103 3828.56
104 3828.56
105 3828.56
106 3828.56
107 3828.56
Splash
Cell Formulas
RangeFormula
O2,O4:O107O2=IF(E2="","",COUNTIF(\$E\$2:E2,E2)&" - "&E2)
P2:P107P2=P1+N2
O3O3=IF(E3="","",COUNTIF(\$E\$2:E119,E3)&" - "&E3)
T4T4=SUM(P1:P252)
T5T5=SUMIF(\$N\$2:\$N\$252,">0")
T6T6=SUMIF(\$N\$2:\$N\$252,"<0")*-1
T7T7=AVERAGE(N2:N251)
T8T8=T5/T6
U9U9=COUNTIF(A2:A254, ">0")
U10U10=COUNTIF(N2:N254,">0")
U11U11=COUNTIF(N2:N254,"<0")
U12U12=MAX(N2:N254)
U13U13=MIN(IF(C2:C252<>"balance",N2:N252,""))
U14U14=AVERAGEIF(N2:N254, ">0")
U15U15=AVERAGEIF(N2:N254, "<0")
U17U17=COUNTIF(C2:C254, "sell")
U19U19=COUNTIFS(C2:C254, "sell", N2:N254, ">0")
T20T20=MAX(FREQUENCY(IF(N2:N254>0,ROW(N2:N254)),IF(N2:N254<=0,ROW(N2:N254))))
T21T21=MAX(FREQUENCY(IF(N2:N255<0,ROW(N2:N255)),IF(N2:N255<=0,ROW(N2:N255))))
T22T22=MAX(Y4:Y254)
T23T23=MIN(Y4:Y254)
T24T24=SUM(K2:K254)
T26T26=U10/U9
V26V26=MIN(IF(TRIM(F2:F252)<>"",N2:N252,""))
V29V29=MIN(IF(F2:F200<>"",N2:N200,""))
U33U33=MIN(IF(C2:C200<>"balance",N2:N200,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

kweaver

Well-known Member
I'm afraid that doesn't help because when that data is copied, only the raw data is copied.
After I did that (again), I wrote the formula in U25 (there are alternate blank lines in the copied data; it's U13 if the blank lines are removed) and still got -42.44

andrewmurray86

New Member
Hmmm OK. Well Thanks for your help, I truly appreciate your time and knowledge here.

kweaver

Well-known Member
Here's another idea. In, say, the Z column (Where there's nothing relating to your data), enter this:

Code:
``=(CLEAN(N2))*1``

and fill that down.

Then, change the formula in U13 to:

Code:
``=MIN(IF(C2:C252<>"balance",Z2:Z252,""))``

And see if that gives you the min value now.

The CLEAN function gets rid of a lot of issues (not all).

Replies
4
Views
130
Replies
16
Views
240
Replies
4
Views
375
Replies
33
Views
766
Replies
2
Views
230

1,129,478
Messages
5,636,564
Members
416,923
Latest member
jarri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back