Calculation Head Do - Please Help !!!

slimimi

Well-known Member
Joined
May 27, 2008
Messages
532
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...
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Watch MrExcel Video

Forum statistics

Threads
1,122,539
Messages
5,596,754
Members
414,097
Latest member
FaeFen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top