+- MacResource (https://forums.macresource.com)
+-- Forum: My Category (https://forums.macresource.com/forumdisplay.php?fid=1)
+--- Forum: Tips and Deals (https://forums.macresource.com/forumdisplay.php?fid=3)
+--- Thread: Excel IF/OR formula question (/showthread.php?tid=95562)
Pages:
1
2
Excel IF/OR formula question - Fritz - 04-10-2010
I'm trying to formula this:
If C12 contains "* Close" or "* Short", then Q12 = 7. If C12 contains neither, then Q12 is zero (or no entry).
I tried below, but the results are wrong, though the formula seems syntaxed (I know, not a word, but you know what I mean, eh?) correctly.
IF(OR(C12="* Close",C12="* Short"),7,0)
I tried at Mr Excel, but I don't understand the suggestion.
Re: Excel IF/OR formula question - N-OS X-tasy! - 04-10-2010
You need to use nested IF statements:
=if(C12="* Close",7,if(C12="* Short",7,0))
EDIT: Left off a closing parenthesis.
EDIT: My formula assumes it is located in cell Q12. If it is not, the statement becomes:
=if(C12="* Close",Q12="7",if(C12="* Short",Q12=7,0))
Re: Excel IF/OR formula question - rich in distress - 04-10-2010
IF does not take wildcards, but this may help:
=IF(OR(RIGHT(C12,5)="Close",RIGHT(C12,5)="Short"),7,"")
substitute smileys with closing parenthesis
Good Luck!!...
Re: Excel IF/OR formula question - Fritz - 04-10-2010
thank you both.
IF didn't take wildcards, as rich said.
rich's formula works, but lemme aks you dis. wtf?
what is RIGHT doing? Does that have something to do with the wildcard situ?
what is the 5 of C12,5 doing?
thanks
Re: Excel IF/OR formula question - Greg - 04-10-2010
RIGHT(C12,5)
...refers to the five rightmost characters of whatever is in cell C12.
Re: Excel IF/OR formula question - Fritz - 04-10-2010
sheesh! I'd never find that.
Re: Excel IF/OR formula question - N-OS X-tasy! - 04-10-2010
Sorry, didn't realize those asterisks were intended as wild cards - the extra space threw me off.
Re: Excel IF/OR formula question - Buzz - 04-10-2010
May be good time for old school logic primer...
A ) There is usually more than one way to skin a cat, so start with any unique results, then work your way through the multiples, compounds, conditionally exclusives, etc., down through the default.
B ) It helps to write out each individual logical component in its most basic form(s), and test it/them separately in hierarchal order first.
C ) After you have established that your individual logical components function properly, combine them one at a time, and check to make sure each successive combination works properly before continuing.
D ) After you have created a functioning compound logical statement, you can then work on streamlining it or making it more elegant and efficient.
This usually allows you to get up and running faster, so you at least have a working solution in hand from which to start optimizing. The most basic logical "B )" components in hierarchal order here were:
If C12 = "Close" then Q12 = 7
If C12 = "Short" then Q12 = 7
If C12 <> "Close" and If C12 <> "Short" then Q12 = 0
If C12 is empty [or for any and all other conditions not listed above] Q12 = 0
There are four distinct options here, noting that the fourth, and last, is the default provided when all other viable options have failed. Assuming you account for other stray stuff in 'C12', so that you are really only looking for 'Close' or 'Short' there, the "C )" assembly would be;
IF(C12 = "Close", 7, IF(C12 = "Short", 7, IF(AND(C12 <> "Close", C12 <> "Short"), 0, 0 ) ) )
After you get working logic that accounts for all of your conditions in proper hierarchal order, then you can streamline for efficiency and elegance as stated in 'D )', rather than starting with 'D )'...
Re: Excel IF/OR formula question - Fritz - 04-11-2010
Thanks again for all your inputs.
I'm not that well versed (duh) in Excel. I normally use it for simple database listing functions.
I am kind of floundering thru it to do these (prolly) simple logic & math functions/formulas.
But the syntax tangles me and the internal help doesn't seem very helpful to the lay person.
I thank my lucky stars I got this far without a diploma.
Re: Excel IF/OR formula question - N-OS X-tasy! - 04-12-2010
I taught myself Excel using only the online help. I think it more than suffices for everything other than VB and pivot tables/charts.