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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Replies
0
Views
143
Replies
4
Views
838
Replies
1
Views
484
Replies
13
Views
527
Replies
6
Views
247

1,127,519
Messages
5,625,278
Members
416,086
Latest member
CaptainGD

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

### Which adblocker are you using?

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

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