Excel IF...THEN

FuturesTrader71

FuturesTrader71

I have a spreadsheet that compares a list of levels against the last market price. It will then pick the next level above the last price and show that as an offer and the next level below the last price as a bid. Here's an example:

10640
10459
10442
10436
10389
<---- last traded price 10346
10342
10330
10320
10310
10290
10270

Last Price: 10346

Formula Cell results:
ASK: 10389
BID: 10342

To get this, I have a very very long IF...THEN statement that is hard to change or follow because it tests the condition on every level.

Is there a better way to do this?

Also, on some days, I might have 12 levels and others I might have 6. It would be great if it could work on both without modifying the formula.
 
=countif(range of levels,">"&index level cell) will give you the number of levels higher than last and so give you an offset to play with...

think that works if i understand the question...

:)

Q1
 
Quote from Htrader:

Here is something I came up with real quick.

Its a slow day for me and I'm bored. :)
More...
I see what you did. Didn't think of that. It might be a bit bulky once I include the markets I'm looking at.
 
Quote from Quiet1:

=countif(range of levels,">"&index level cell) will give you the number of levels higher than last and so give you an offset to play with...

think that works if i understand the question...

:)

Q1
More...
Hmmm... I tried to play with this, but it doesn't quiet work.
 
Quote from FuturesTrader71:

I have a spreadsheet that compares a list of levels against the last market price. It will then pick the next level above the last price and show that as an offer and the next level below the last price as a bid. Here's an example:

10640
10459
10442
10436
10389
<---- last traded price 10346
10342
10330
10320
10310
10290
10270

Last Price: 10346

Formula Cell results:
ASK: 10389
BID: 10342

To get this, I have a very very long IF...THEN statement that is hard to change or follow because it tests the condition on every level.

Is there a better way to do this?

Also, on some days, I might have 12 levels and others I might have 6. It would be great if it could work on both without modifying the formula.
More...


why not use Excel's "MIN" & "MAX" functions to create single cells showing the minimum price from the "ASK" column, and the maximum price from the "BID" column. Then, if necessary, you could just reference those single cells for any conditional statements you might need?
 
You must log in or register to reply here.
Back
Top