Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
=ABS
I need to calculate margin required for trading,
if there is 1 buy & 1 sell, I need result as 1 & -1, for example: cell cell A7 buy B7 1 A9 buy B9 1 A10 sell B10 2 So far my formula in cell H10 is : =IF(OR(A10="buy",A10="sell"),ROUND(((H$3*B10)*1000 *0.05)*C10,2)," ") how do I make it work to give me a -ve result? please help. Thanks. Mukesh |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
=ABS
Not sure that I understand what you are trying to achieve but if you enclose
the entire Round function in parenthesis and multiply by -1 then it should return a negative result; unless the answer is already negative in which case it would return a positive result. =IF(OR(A10="buy",A10="sell"), -1*(ROUND(((H$3*B10)*1000*0.05)*C10,2))," ") If this does not answer your question then perhaps post a little more information including sample values for H3 and C10. Then tell us what result you are getting and what you want the result to be. -- Regards, OssieMac "Mukesh" wrote: I need to calculate margin required for trading, if there is 1 buy & 1 sell, I need result as 1 & -1, for example: cell cell A7 buy B7 1 A9 buy B9 1 A10 sell B10 2 So far my formula in cell H10 is : =IF(OR(A10="buy",A10="sell"),ROUND(((H$3*B10)*1000 *0.05)*C10,2)," ") how do I make it work to give me a -ve result? please help. Thanks. Mukesh |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
=ABS
what I want to do is basically balance both sides.
1 buy & 1 sell = 0 2 buy & 1 sell = +1 or if its 2 sell & 1 buy = -1 and so on..... A B 21 sell 1 23 sell 1 24 buy 2 25 buy 1 26 sell 2 How I do I put it in formula? your help is highly apprecaited. Thanks. Mukesh "OssieMac" wrote: Not sure that I understand what you are trying to achieve but if you enclose the entire Round function in parenthesis and multiply by -1 then it should return a negative result; unless the answer is already negative in which case it would return a positive result. =IF(OR(A10="buy",A10="sell"), -1*(ROUND(((H$3*B10)*1000*0.05)*C10,2))," ") If this does not answer your question then perhaps post a little more information including sample values for H3 and C10. Then tell us what result you are getting and what you want the result to be. -- Regards, OssieMac "Mukesh" wrote: I need to calculate margin required for trading, if there is 1 buy & 1 sell, I need result as 1 & -1, for example: cell cell A7 buy B7 1 A9 buy B9 1 A10 sell B10 2 So far my formula in cell H10 is : =IF(OR(A10="buy",A10="sell"),ROUND(((H$3*B10)*1000 *0.05)*C10,2)," ") how do I make it work to give me a -ve result? please help. Thanks. Mukesh |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
=ABS
Try this and see if it returns what you want.
=SUMIF(A21:A26,"buy",B21:B26)-SUMIF(A21:A26,"sell",B21:B26) -- Regards, OssieMac "Mukesh" wrote: what I want to do is basically balance both sides. 1 buy & 1 sell = 0 2 buy & 1 sell = +1 or if its 2 sell & 1 buy = -1 and so on..... A B 21 sell 1 23 sell 1 24 buy 2 25 buy 1 26 sell 2 How I do I put it in formula? your help is highly apprecaited. Thanks. Mukesh "OssieMac" wrote: Not sure that I understand what you are trying to achieve but if you enclose the entire Round function in parenthesis and multiply by -1 then it should return a negative result; unless the answer is already negative in which case it would return a positive result. =IF(OR(A10="buy",A10="sell"), -1*(ROUND(((H$3*B10)*1000*0.05)*C10,2))," ") If this does not answer your question then perhaps post a little more information including sample values for H3 and C10. Then tell us what result you are getting and what you want the result to be. -- Regards, OssieMac "Mukesh" wrote: I need to calculate margin required for trading, if there is 1 buy & 1 sell, I need result as 1 & -1, for example: cell cell A7 buy B7 1 A9 buy B9 1 A10 sell B10 2 So far my formula in cell H10 is : =IF(OR(A10="buy",A10="sell"),ROUND(((H$3*B10)*1000 *0.05)*C10,2)," ") how do I make it work to give me a -ve result? please help. Thanks. Mukesh |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
=ABS
Just perfect, thanks a ton.
Mukesh "OssieMac" wrote: Try this and see if it returns what you want. =SUMIF(A21:A26,"buy",B21:B26)-SUMIF(A21:A26,"sell",B21:B26) -- Regards, OssieMac "Mukesh" wrote: what I want to do is basically balance both sides. 1 buy & 1 sell = 0 2 buy & 1 sell = +1 or if its 2 sell & 1 buy = -1 and so on..... A B 21 sell 1 23 sell 1 24 buy 2 25 buy 1 26 sell 2 How I do I put it in formula? your help is highly apprecaited. Thanks. Mukesh "OssieMac" wrote: Not sure that I understand what you are trying to achieve but if you enclose the entire Round function in parenthesis and multiply by -1 then it should return a negative result; unless the answer is already negative in which case it would return a positive result. =IF(OR(A10="buy",A10="sell"), -1*(ROUND(((H$3*B10)*1000*0.05)*C10,2))," ") If this does not answer your question then perhaps post a little more information including sample values for H3 and C10. Then tell us what result you are getting and what you want the result to be. -- Regards, OssieMac "Mukesh" wrote: I need to calculate margin required for trading, if there is 1 buy & 1 sell, I need result as 1 & -1, for example: cell cell A7 buy B7 1 A9 buy B9 1 A10 sell B10 2 So far my formula in cell H10 is : =IF(OR(A10="buy",A10="sell"),ROUND(((H$3*B10)*1000 *0.05)*C10,2)," ") how do I make it work to give me a -ve result? please help. Thanks. Mukesh |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
=ABS
This fomula works fine
=SUMIF(A21:A26,"buy",B21:B26)-SUMIF(A21:A26,"sell",B21:B26) but if I have =SUMIF(A21:A26,"buy",B21:B26)-SUMIF(A21:A26,"sell",B21:B26)*100 I am getting a wrong answer, what could be wrong? Thanks. Mukesh "Mukesh" wrote: Just perfect, thanks a ton. Mukesh "OssieMac" wrote: Try this and see if it returns what you want. =SUMIF(A21:A26,"buy",B21:B26)-SUMIF(A21:A26,"sell",B21:B26) -- Regards, OssieMac "Mukesh" wrote: what I want to do is basically balance both sides. 1 buy & 1 sell = 0 2 buy & 1 sell = +1 or if its 2 sell & 1 buy = -1 and so on..... A B 21 sell 1 23 sell 1 24 buy 2 25 buy 1 26 sell 2 How I do I put it in formula? your help is highly apprecaited. Thanks. Mukesh "OssieMac" wrote: Not sure that I understand what you are trying to achieve but if you enclose the entire Round function in parenthesis and multiply by -1 then it should return a negative result; unless the answer is already negative in which case it would return a positive result. =IF(OR(A10="buy",A10="sell"), -1*(ROUND(((H$3*B10)*1000*0.05)*C10,2))," ") If this does not answer your question then perhaps post a little more information including sample values for H3 and C10. Then tell us what result you are getting and what you want the result to be. -- Regards, OssieMac "Mukesh" wrote: I need to calculate margin required for trading, if there is 1 buy & 1 sell, I need result as 1 & -1, for example: cell cell A7 buy B7 1 A9 buy B9 1 A10 sell B10 2 So far my formula in cell H10 is : =IF(OR(A10="buy",A10="sell"),ROUND(((H$3*B10)*1000 *0.05)*C10,2)," ") how do I make it work to give me a -ve result? please help. Thanks. Mukesh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|