How to write a complex conditional in excel?

Question

I'm trying to teach myself excel and I've gotten pretty proficient with the basic commands. I can also write simple conditionals (not sure what else to call them, but I mean just a basic if, then). My trouble is with the more complicated conditionals.

I gave myself a project. Build a spreadsheet that tells you how whether to buy, don't buy, or sell stocks (and no I'm not actually gonna use it for that purpose lol). Column 1 is price yesterday, column 2 is price today. I want column three to say the action.

So the command I want will say if the price is less than 85% of where it was yesterday, "buy". If the price is between 85% and 115% of where it was yesterday, take no action, and if the price is above 115% of yesterday's, "sell."

Thanks in advance for any help

Answer 1

Try this:



=IF(A1/B1<0.85, "buy", IF(A1/B1>1.15, "sell", ""))



Yes, there's an IF in and IF! What this means is



If ratio is below 0.85 print "buy"

otherwise (

if ratio is above 1.15 print "sell"

otherwise print ""

)



And again, in other words: If the ratio is above .85 you check the result again to see if it's above 1.15.



Some programming languages and spreadsheets include so-called "switch-statements" or switch functions. Then you can write something like



switch(A1/B1<0.85, "buy", A1/B1>1.15, "sell", "")



because it accepts a format (condition1, output1, condition2, output2, ..., defaultoutput). But I couldn't find a switch function in Excel.

Answer 2

The great solution to get what you desire might be to list a macro to provide the preferred formatting after which insert the code for the stipulations. If you do not know VBA good adequate to code the stipulations you're probably not to be equipped to make the alterations you might ought to whatever you would replica. To list the macro; a million. Set up your worksheet in order that you're in a position to opt for after which layout the cells of curiosity. two. Select Tools, Macro, Record New Macro, OK three. Select the cells to be formatted. four. Format the cells the best way you desire for one set of stipulations. five. Format the cells once more for every different situation. 6. Select Tools, Macro, Stop Recording. When you're performed recording your macro(s) pass to VBA; discover the code within the Modules folder; replica the formatting code to in which you wish to have it and insert the stipulations for every layout.

Answer 3

Check out the IIF command (yes, two "I"s before the "F") The syntax is IIF (condition), (True statement),)(False statement). Check the help file. Also, check out http://www.pscode.com for great VBA samples with excel.