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.
VBA would be much more efficient since each line doesn't need to be interpreted. Would probably offer more flexibility too.
=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
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.
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?