formula to "choose" a cell from a range by criteria

Hi all

I wonder if there's even an elegant way to do the following:

My spreadsheet is updating live from my broker's server, so I have two columns: column A: Strike prices, column B:option's price
for instance:
____________A_____________B
1 May18th 18.5 Call__________$0.38
2 May18th 19 Call____________$0.30
3 May18th 19.5 Call__________$0.28
and so on..

I have an additional cell in which I define a "budget" for the purchase, let's assume it's C3

My desired formula need to strive to buy the LOWEST strike price (column A) possible, but know that is can't pay more than the value in C3. so all I need is that the formula populate the cell it's written in with the strike price chosen, so in the example above and assuming C3(budget)= 0.35 the formula should return "19" (in the spreadsheet column A is only the strike (185/19/19.5 etc) without the date and option type, so the formula really just need to copy A2 as is).


My next step is to have a second formula in the neighboring cell to be populated with the price (column B) of the strike chosen earlier, so that in the end I'll have a log of what strike excel "chose" to buy and what he paid for it.



ANY IDEAS? thanks!

It is not clear to me what it is that you need. You may want to post a workbook with input, desired result and a more concise explanation of what it is that you need.

thanks Jarko, I attached a demo to demonstrate my needs.. I suppose it's a mix of =max BUT ALSO =IF(X=<Y,...) combination... not that huge on excel so I'm not sure how to do it.. hoping you will have some idea

johnny

Hi,
Pls find attached file,

a new challenge on the same line as the issue above - this one is a tad more complicated.. I think all criteria is mentioned in the attached spreadsheet - if anything is unclear I'd be happy to elaborate. I tried working with the solutions provided me here and failed.

thanks in advance to the genius who solves this

at Vlady's request I'm uploading a new spreadsheet with the problem, with some clarifications and expected outcomes to help. Vlady by the way is the mind who solved the last problem. Thanks!

hi,
See attached file for your issue!

Originally Posted by duanzhuanming

hi,
See attached file for your issue!

duanzhuanming - it's working in your spreadsheet, but when I try to copy-paste it to my real spreadsheet (and adjusting the cells selections appropriately) the formulas return either "-1111111111" or weird, false values. see attached spreadsheet - can you spot the problem?

thanks

You have to confirm all array formulas by pressing Ctrl+shift+Enter!
see attached file!

Originally Posted by ryefield

duanzhuanming - it's working in your spreadsheet, but when I try to copy-paste it to my real spreadsheet (and adjusting the cells selections appropriately) the formulas return either "-1111111111" or weird, false values. see attached spreadsheet - can you spot the problem?

thanks

Originally Posted by duanzhuanming

You have to confirm all array formulas by pressing Ctrl+shift+Enter!
see attached file!

oh thanks, I don't have a clue what array formulas are so I'll just nod and pretend I do.. btw, could you please pretend like this is a challenge for you next time, to make all the rest of us here fell a bit better?

this man is a spreadsheet ninja.