Excel IF...THEN

Discussion in 'Trading Software' started by FuturesTrader71, Jun 24, 2005.

  1. 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.
     
  2. Post your spreadsheet, and I might be able to help!
     
  3. VBA would be much more efficient since each line doesn't need to be interpreted. Would probably offer more flexibility too.
     
  4. definatly
     
  5. Htrader

    Htrader Guest

    Here is something I came up with real quick.

    Its a slow day for me and I'm bored. :)
     
    • et.xls
      File size:
      17 KB
      Views:
      76
  6. Quiet1

    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
     
  7. Ok. Here is a sample of it.

    Your time and help is appreciated.
     
  8. 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.
     
  9. Hmmm... I tried to play with this, but it doesn't quiet work.
     

  10. 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?
     
    #10     Jun 24, 2005