ExcelBanter

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - =ABS (https://www.excelbanter.com/excel-discussion-misc-queries/230041-%3Dabs.html)

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

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

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

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

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

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