Hello there - i am trying to perform a calculation which is giving me a head do and i just cant figure out how to do it.
BACKGROUND
I have a sheet which has 8 Currency Slots.
Each Slot allows you to enter data into 10 Positions (5 x Buy : 5 x Sell)
Each position allows you to enter a Price & Lot Size.
Total of 80 Positions altogether (10 for each Currency Slot x 8 Slots).
Each currency slot can be changed so, therefore, you could end up using 2 Slots for the same currency, eg, AUD/USD.
Now - i am trying to calculate "USED MARGIN".
Used Margin is basically Lot Size x 50
PROBLEM
If you have 2 Lot Sells - This will cost $100.00 in Margin (that's fine).
HOWEVER
If you had 2 Lot Sells and 2 Lot Buys (on the same currency pair) - This will also only cost you $100.00 because you are, in effect, hedging your position and the broker only charges you 1 time.
EXAMPLE
If you had 1 Lot Sell and 2 Lot Buys (this would cost $100.00 in margin)
If you had 2 Lot Sell and 3 Lot Buy (this would cost $150.00 in margin).
Make sense so far???
So if i had 1.3 Lot Buy and 1.7 Lot Sell then the calculation would need to be 1.3 x 50.00 + 0.4 x 50.00 (0.4 is the difference between 1.3 and 1.7).
How can i turn this into a formula which ALSO looks at the Currency Slot (Column A) before it tally's Buys and Sells against eachother???
Example, i might have positions on the AUD/USD in Slot 1 and Slot 3 and i would need Slot 1 Buys and Sells and Slot 3 Buys and Sells to tally against eachother, work out the difference and then perform the calc.
Arrrrgggghhh - this is even a head do trying to explain
)))
Hope someone can shed some light for me...
BACKGROUND
I have a sheet which has 8 Currency Slots.
Each Slot allows you to enter data into 10 Positions (5 x Buy : 5 x Sell)
Each position allows you to enter a Price & Lot Size.
Total of 80 Positions altogether (10 for each Currency Slot x 8 Slots).
Each currency slot can be changed so, therefore, you could end up using 2 Slots for the same currency, eg, AUD/USD.
Now - i am trying to calculate "USED MARGIN".
Used Margin is basically Lot Size x 50
PROBLEM
If you have 2 Lot Sells - This will cost $100.00 in Margin (that's fine).
HOWEVER
If you had 2 Lot Sells and 2 Lot Buys (on the same currency pair) - This will also only cost you $100.00 because you are, in effect, hedging your position and the broker only charges you 1 time.
EXAMPLE
If you had 1 Lot Sell and 2 Lot Buys (this would cost $100.00 in margin)
If you had 2 Lot Sell and 3 Lot Buy (this would cost $150.00 in margin).
Make sense so far???
So if i had 1.3 Lot Buy and 1.7 Lot Sell then the calculation would need to be 1.3 x 50.00 + 0.4 x 50.00 (0.4 is the difference between 1.3 and 1.7).
How can i turn this into a formula which ALSO looks at the Currency Slot (Column A) before it tally's Buys and Sells against eachother???
Example, i might have positions on the AUD/USD in Slot 1 and Slot 3 and i would need Slot 1 Buys and Sells and Slot 3 Buys and Sells to tally against eachother, work out the difference and then perform the calc.
Arrrrgggghhh - this is even a head do trying to explain
)))Hope someone can shed some light for me...
