98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » IF Function
IF Function
Resolved · Medium Priority · Version 2016
Thu 24 Jan 2019, 15:27Delegate Kay said...
Kay has attended:
Excel Advanced course
Excel Advanced - Formulas & Functions course
IF Function
I have a query regarding IF statements in Excel. I am trying to construct a simple "if" statement so to speak which has a range however it doesn't appear to work
IF(B9:N9="x","YES","")
B9:N9 = range
If you provide any guidance on this it would be very much appreciated.
For more information, check out our Excel data analysis course London.
Thu 24 Jan 2019, 16:59Trainer Claire said...
RE: IF Function
Hi Kay,
The problem is coming because you're referring to a range and not a single cell.
So this works fine: =IF(A1="X", "Yes", "")
But you are asking excel to take the value of all the cells in the range to be "X". And it would be multiple Xs where that column contained an X.
I think - and I can't say for sure as I don't know what else is in your sheet - that you need an IF for each line and then possibly something to evaluate the final outcome.
Hope this helps.
Claire
Wed 30 Jan 2019, 10:27Delegate Kay said...
RE: IF Function
Hi Claire,
Thanks for coming back to me.
Just to provide some clarity on this.
The range is from B9:N9 where each of the single cells are either left blank or have a "X"
What I am trying to achieve is to create an "IF" formula to read each cell and return "Yes" if any of the values between B9 to N9 have an "X". If the range B9:N9 is blank then it returns ""
I have tried to build it myself but returns an error as not sure if I should also be including "OR" in the formula (see below)
IF(B9="Yes",C9="Yes",C9="Yes",D9="Yes",E9="Yes"),"Yes","")
Thanks
Wed 30 Jan 2019, 10:38Trainer Claire said...
RE: IF Function
Hi Kay,
I think you want an AND to check the status of the cells. I just mocked this up and this works:
=IF(AND(A1="x", A2="x", A3="x",A4="x", A5="x", A6="x"), "Yes", "")
Does that help?
Claire
Wed 30 Jan 2019, 11:37Delegate Kay said...
RE: IF Function
Hi Claire,
Thanks for your reply. My equations reads like this
=IF(AND(F4="X",G4="X",H4="X",I4="X",J4="X",K4="X",L4="X",M4="X",N4="X",O4="X"), "Yes", "")
However it still doesn't seem to be working.
Any pointers as to why?
Thanks
Wed 30 Jan 2019, 11:59Trainer Claire said...
RE: IF Function
Have you been able to pinpoint which bit is not working?
If you take out the AND and run it in a cell does it give you TRUE? If not then what does it return?
Once you have confirmed this you then need to check what your IF is doing.
You can do this using the Insert Function button to the left of the formula bar. Click into the cell with your formula in it, then click Insert Function. Then you get a dialog box which breaks out the component parts of the IF and tells you what each part evaluates to.
So first thing to check is that the test gives you true or false.
Please let me know how you get on.
Claire
Wed 30 Jan 2019, 12:07Delegate Kay said...
RE: IF Function
Hi Claire,
When I take out the "AND" and run it in a cell - it tells me I have too many arguments - is there any suggestions as to how to overcome this?
Thanks
Wed 30 Jan 2019, 12:16Trainer Claire said...
RE: IF Function
I've taken your formula from earlier in the thread and it works OK in my mock up.
Here's what I'm using (I added a "no" for the false value for clarity):
=IF(AND(F4="X",G4="X",H4="X",I4="X",J4="X",K4="X",L4="X",M4="X",N4="X",O4="X"), "Yes", "No")
Is that exactly what you have in your sheet?
What is being returned by your function?
Claire
Wed 30 Jan 2019, 12:27Delegate Kay said...
RE: IF Function
Hi Claire
Apologies for this but I am using the exact formula as above but in my function button this is what I see
Logical test
AND(F4="X",G4="X",H4="X",I4="X",J4="X",K4="X",L4="X",M4="X",N4="X",O4="X")= FALSE
Value if True = Yes
Value if True = No
which return No even when there is X in the cells above.
Wed 30 Jan 2019, 12:34Trainer Claire said...
RE: IF Function
Hi Kay,
Can you send me your file?
Attaching files to here is never straight forward...
Can you send it to claire.chandler@stl-training.co.uk?
I think this is the best way forward or we'll go round in circles for days...
Claire
Wed 30 Jan 2019, 12:54Delegate Kay said...
RE: IF Function
Hi Claire
I have sent you the file to the above mailbox. Please reply directly to my email address as I can find I am unable to open attached files in this forum. Thanks again
Wed 30 Jan 2019, 14:14Trainer Claire said...
RE: IF Function
Hi Kay,
your formula is fine. You don't have enough X's in the range you're testing. I've put this in your document and am sending this back to you.
thanks
Claire
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Stop Formula Returning A "#DIV/0" ErrorIf a formula returns a #DIV/0 error message there is a way to avoid such results. |
