# Calculation Head Do - Please Help !!!

#### slimimi

##### Well-known Member
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...

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,164,013
Messages
5,834,925
Members
430,326
Latest member
tomwax46

### 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

### 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