Anyone here know EXCEL (better than me) ??

[rjay]

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]

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]

Works a treat. Thanks very much.

 

[rjay]

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]

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]

Negative numbers are always smaller than positive numbers.

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

 

[rjay]

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

 

[swandro]

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]

Now there's something I never would have figured out Thanks very much swandro.

 

[ZigZag]

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]

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]

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