formula that adds/subtracts from 00 to 32 vs 00 to 100

I trade the futures markets and I've created a spreadsheet that keeps track of my trades.

My formula is pretty simple:

a1 = ZB (commodity)
b1 = B or S (Buy or Sell)
c1 = entry price
d1 = exit price
e1 = profit/loss
f1 = formula the looks at a1 and looks up the appropriate point (tic) value

the formula in e1 "=if(b1="S",c1-d1,d1-c1)"

This formula works great on "most" markets except the Bond market which trades in increments of 1/32 (00 to 31 or 32 points (ticks)).

Let's say I buy at 155.30 and exit at 156.02 it should show 04 points (tics) in profit (31, 00, 01, 02) but it shows 72 points (tics) in profits because its counting to 100.

So, what do I need to add to the e1 formula so it counts in 1/32's when "a1 - ZB"?

Any help will be appreciated.

Bruce

This is the formula that you need; but I got a bit confused about where it fits into your overall formula:

=(32*INT(B1)+100*MOD(B1,1))-(32*INT(A1)+100*MOD(A1,1))

assumes buy in A1 and sell in B1....

This is the exact situation for which the DOLLARDE() and DOLLARFR() functions are included.

Use DOLLARDE() to convert your dollar.32nds of a dollar to decimal dollars https://support.office.com/en-us/art...d-a38476693427
subtract the numbers
then, if desired, convert back to dollar.32nds of a dollar using the DOLLARFR() function https://support.office.com/en-us/art...4-3042c5d4f495

something like =DOLLARFR(DOLLARDE(155.3,32)-DOLLARDE(156.02,32),32) returns -0.04 (meaning -4/32)

Post a sample sheet.

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Try this. If this does not work then read Post 5 again, and act on it.

=IF(C90="ZB",IF(E90="S",(32*INT(I90)+100*MOD(I90,1))-(32*INT(G90)+100*MOD(G90,1)),(32*INT(G90)+100*MOD(G90,1))-(32*INT(I90)+100*MOD(I90,1))),IF(E90="S",G90-I90,I90-G90))

Using my solution, I would probably do something like =DOLLARDE(number,IF(C90="ZB",32,100))

How can you tell that trade 2 is a Bond trade, and is subject to tics????

I'm an analytical chemist, not a financier and need to have these things explained!!!

I'm confuses

Is G buy price when buying AND selling??? or what??

The penny drops, finally. Back shortly.

No wonder the world is in a mess. Let me get this right.

If it's a BUY, the Profit/loss is I minus G.

if it's a sell the Profit/Loss is I minus G?

If not, then what?

OK. I'll stick to Chemistry, methinks. I'll sort it asap. Off to eat now (late p.m. in Europe).

It did just need the /100, along with another set of parentheses:

=IF(C3="ZB3",IF(E3="S",((32*INT(G3)+100*MOD(G3,1))-(32*INT(I3)+100*MOD(I3,1)))/100,((32*INT(I3)+100*MOD(I3,1))-(32*INT(G3)+100*MOD(G3,1)))/100),IF(E3="S",G3-I3,I3-G3))

Hope this is it!

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

You're welcome. Not the best though. I made a total mess of another one a little earlier today.