• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF formula for exceptions to a rule

In the below table I would like Column K to automatically say Not Applicable if one of the 8 departments mentioned in cells N11 to N18 is entered in Column C, which is imported from a different spreadsheet.

For example, rows 6,7 and 8 are all in departments that require a new starters audit, but row 9 is not. They work in IT, for example.
Code:
    Column G    Column H Column I   Column K
          Starters Audit Assigned To Audit due Audit complete
Row 6 JSW3 15/02/2015 Yes Complete
Row 7 JSW3 03/03/2015 Yes Complete
Row 8 JSW3 27/02/2015 Yes Complete
Row 9 JSW3
 
Last edited by a moderator:
In the below table I would like Column K to automatically say Not Applicable if one of the 8 departments mentioned in cells N11 to N18 is entered in Column C, which is imported from a different spreadsheet.

For example, rows 6,7 and 8 are all in departments that require a new starters audit, but row 9 is not. They work in IT, for example.




Column G Column H Column I Column K
Starters Audit Assigned To Audit due Audit complete
Row 6 JSW3 15/02/2015 Yes Complete
Row 7 JSW3 03/03/2015 Yes Complete
Row 8 JSW3 27/02/2015 Yes Complete
Row 9 JSW3
Can you attach sample file. It is useful to know the exact answer.
 
Dear Anthony

A forumla like

=IF(SUMPRODUCT(--(C9=$N$11:$N$18))=1,"Not applicable","Yes") in K9 wilol put Not applicable if the deptpartment in C9 is in N11:n18 and yes otherwise.

Does this do what you need?
 
I have tried again and this does work :)

What I need now is for cell K9 to populate immediately if the department is one of those listed between cells N11 and N18. However, if the department is not listed I only need it to say yes, if the audit has been assigned, allocated a date and completed, with entries in G9, H9 and I9.

Thanks in advance.
 
or K9 could populate immediately with 'Not applicable' if the department is one of those listed between cells N11 and N18, and say 'No' if it is not, UNLESS cells G9, H9 and I9 have been filled in, in which case it will read 'Yes'.
Presumably this would read something like :
=IF(SUMPRODUCT(--(C9=$N$11:$N$18))=1,"Not applicable","No")UNLESS/OR.....?
 
That's great :)
If I want the cells in column K to default to a '-' until data is added to columns G, H and I, what cell format would I need ?
 
Another question for you is how would I get cell H6 to show 'Not applicable' if K6 says 'Not applicable', otherwise it will take the start date (in F6) and add 45 days to give a review date ?
Presumably that would be similar to the first formula you gave me, or would it be different with the data being a date, rather than just numbers ?
 
Hi Anthony ,

It would be nice if you could put all your questions in one post ; asking them one after the other makes it difficult for those who wish to answer your latest question , when it is an extension of an earlier question which has already been answered by one member.

If I now have to answer your latest question , I cannot do so unless I go through all the earlier posts in this thread ; not many would want to do that.

Narayan
 
No problem, thanks for coming back to me Narayank991.

If I want the cells in column K to default to a '-' until data is added to columns G, H and I, would that be down to the cell format, or would I need to change the formula ?
Also, how would I get cell H6 to show 'Not applicable' if K6 says 'Not applicable', otherwise it will just take the start date (in F6) and add 45 days to give a review date ?
Thanks in advance
 
I don't think I'm far away from this. Can anybody tweak the formula for me. I just need to get the "sum(F15+45)" element of the formula to take cell F15 and add 45 days to give a due date.

=IF(K15="Not applicable", "Not applicable", IF(K15="Yes", "sum(F15+45)", IF(K15="No", "sum(F15+45)", "")))

Any help would be greatly appreciated.

Thanks
 
I am assuming F15 contains date..right?

Removed " & +

IF(K15="Not applicable", "Not applicable", IF(K15="Yes", sum(F15,45), IF(K15="No", sum(F15,45), ""))

Edit:

I did not test the formula..
 
Last edited:
Hi ,

Since the output is the same whether K15 is Yes or No , there is no need to test for it.

=IF(K15 = "Not Applicable" , K15 , F15 + 45)

should do the job.

Since the SUM function is used when more than 2 inputs are being summed , it does not need to be used here.

Narayan
 
Hi Anthony ,

If you want a cell to display a hyphen instead of a blank , I doubt that it can be done using a cell format ; if you have a zero in a cell , you can use a custom format to display a hyphen. You can modify the formula to either put a zero or a hyphen directly in a cell.

Narayan
 
Thanks Asheesh, I'm getting a circular reference unfortunately. I have the following formula in cell K15

=IF(SUMPRODUCT(--(C10='Data Summary'!$O$14:$O$23))=1,"Not applicable",IF(AND(G10<>"",H10<>"",I10<>""),"Yes","No"))
in cell K15

I will have to upload a sample file from my mobile unfortunately as cannot upload from this pc
 
Hi Anthony ,

The circular reference is inevitable.

Your formula in K15 is checking for H15 being blank / non-blank ; how can the formula in H15 check for the data in K15 ?

Narayan
 
Given that I am getting a circular reference, which is inevitable, how do I get H15 to give me a date, rathert than the $0.00 it is giving me at the moment ? Changing the cell format doesn't help as it just gives me 00/01/1900.
 
Hi Narayan, I have created a dummy column, which I have hidden to get rid of the circular reference.

How would I amend the formula in K15 to get the hyphen where there is no information in cells G, H or I. At the moment the formula reads

=IF(SUMPRODUCT(--(C15='Data Summary'!$O$14:$O$23))=1,"Not applicable",IF(AND(G15<>"",H15<>"",I15<>""),"Yes","No"))

Thanks
 
Hi Anthony ,

I do not know how the dummy column gets rid of the circular reference. Can you post the formula in K15 , as well as the formula in this dummy column ?

We know that the formula in H15 has to refer to the contents of K15 , or the dummy column ; to avoid the circular reference , the formulae in K15 and the dummy column should not refer to H15. If this is so , then the earlier posted formula should work.

Narayan
 
Back
Top