Anyone here know EXCEL (better than me) ??

rjay · May 30, 2006

I've almost finished a little Excel spreadsheet for myself that works out the % of account I should risk on the next trade. However, what I can't figure out is to make sure that it never suggests more than, say, 20%. The final formula is "Y999=SUM(Y3/Y998)" ..... how can I make sure that Y999 never shows more than 20% ??

Rhody Trader · May 30, 2006

rjay said:
I've almost finished a little Excel spreadsheet for myself that works out the % of account I should risk on the next trade. However, what I can't figure out is to make sure that it never suggests more than, say, 20%. The final formula is "Y999=SUM(Y3/Y998)" ..... how can I make sure that Y999 never shows more than 20% ??

Use this:

Y999=Min(Y3/Y998, 0.2)

P.S.: You don't need to use SUM( ) when you're only including one value. Sum is for adding multiple values such as SUM(1,2,3) which would produce 5 (1+2+3).

rjay · May 30, 2006

Works a treat. Thanks very much.

rjay · Jul 21, 2006

I was wrong when I said this worked

=MIN(Y2-1, 0)

still gives me a minus figure (-0.119078947 in this instance). Any ideas why ?

Rhody Trader · Jul 21, 2006

rjay said:
I was wrong when I said this worked

=MIN(Y2-1, 0)

still gives me a minus figure (-0.119078947 in this instance). Any ideas why ?

If Y2-1 = -0.119078947 then that's what should come up.

The Min function returns the lowest of the values included. Since -0.119078947 is less than 0, that is what it will return.

If you think it should be returning 0, then you should probably be using the Max function.

gc1 · Jul 21, 2006

Negative numbers are always smaller than positive numbers.

You can use ABS() to make numbers positive if you want.

rjay · Jul 21, 2006

I want to return a positive number with a minimum of 0 ..... so, still not sure what I'm supposed to be using.

swandro · Jul 21, 2006

You need to use an IF condition.

=IF((Y3/Y998)<0,0,IF((Y3/Y998)>20,20,Y3/Y998))

This will give you a minimum value of 0, a maximum value of 20, otherwise the exact value

rjay · Jul 21, 2006

Now there's something I never would have figured out

Thanks very much swandro.

ZigZag · Sep 29, 2007

Excel IF Function

Help Please.
Using the IF fuction I get the normal response true/False.
But this what I need;
E.G
Cell1 = 100
Cell 2= 110
If cell 2 > than Cell 1 by 20 give actual number.
In my example, as cell 2 is not greater by 20 the response I want would be 100.
e.g 2
Cell 1 = 100
cell 2= 125
As cell 2 > than cell 1 by 20 the response I want would be 120
Would appreciate any help
ZigZag

Trader333 · Sep 29, 2007

Try this,

Assuming that C1 is Cell 1 and C2 is Cell2 then the formula that should work is:

=IF((C2-C1)>20,C2,100)

Good Luck

Paul

Glenn · Sep 29, 2007

ZigZag said:
Help Please.
Using the IF fuction I get the normal response true/False.
But this what I need;
E.G
Cell1 = 100
Cell 2= 110
If cell 2 > than Cell 1 by 20 give actual number.
In my example, as cell 2 is not greater by 20 the response I want would be 100.
e.g 2
Cell 1 = 100
cell 2= 125
As cell 2 > than cell 1 by 20 the response I want would be 120
Would appreciate any help
ZigZag

What do you want to happen when your calculated number is less than or equal to 100 ? Or does this not occur ?

Glenn

Anyone here know EXCEL (better than me) ??

rjay

Active member

Rhody Trader

Senior member

rjay

Active member

rjay

Active member

Rhody Trader

Senior member

gc1

Well-known member

rjay

Active member

swandro

Active member

rjay

Active member

ZigZag

Active member

Trader333

Glenn

Experienced member

Similar threads