• 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.

EITHER OR formula

A

Anthony Pinnegar

Member
I need a formula in cell M6 that will read 'No', if cell B6 shows the words SUP, or SEC. If B6 reads FE it will read 'Yes'.

I then need H6 to read 'Not Applicable' if M6 reads 'No' and L6 reads 'In List' (which is a formula already set up). If H6 does not read 'Not Applicable' the cell should take the date in cell F6 and add 45 days, showing this date in H6.

I have tried to upload an example spreadsheet and I can't unfortunately.

Thanks in advance.

Anthony
 
You can paste this formula in cell M6:

=IF($B$6="SUP","No",IF($B$6="FE","Yes","NEITHER"))

You can paste this formula in cell H6:

=IF(AND($M$6="No",$L$6="In List"),"Not Applicable",$F$6+45)

-> Remember to format cell H6 as a date in order to make this display right...

You've got several combinations that aren't addressed in your original post...

What do you want M6 to say if B6 is neither SUP nor FE?

What do you want H6 to say if L6 does not say "In List"?
 
Last edited:
Anthony
Only certain file types are accepted
Files must be less than 1MB in size
Try saving it as a *.xlsb (Excel Binary) file type
 
Hi Eibe, in brief I would like G6 to read 'Not Applicable' if B6 reads either 'SUP' or 'SEC'. If B6 is neither of these (ie/ it will read FE) I want G6 to take the date from F6 and add 45 days.

Thanks Hui :)
 
So in G6, you need the following formula:

=IF(OR($B$6="SUP",$B$6="SEC"),"Not Applicable",$F$6+45)

->Remember to format cell G6 as a date in order to make this display right...
 
Eibe, that works well thanks :D
When I copy the formula down into cells further down the spreadsheet the date format returns #VALUE! when it should read with a '-'.
Any ideas how to get round this ?
 
For G6:

=IF(ISBLANK(B6),"-",IF(OR(B6="SUP",B6="SEC"),"Not Applicable",F6+45))

For H6:

=IF(OR(ISBLANK(M6), ISBLANK(L6)),"-",(IF(AND(M6="No",L6="In List"),"Not Applicable",F6+45)))

For M6:

=IF(ISBLANK(B6),"-",IF(B6="SUP","No",IF(B6="FE","Yes","NEITHER")))

Let me know...
 
Hi Eibe, I got the formula to work using your second suggestion and only need to sort the formatting out now. When I copy the formula down the spreadsheet I get a #VALUE! in subsequent rows that have not had data entered yet. To tidy the spreadsheet up it should read '-'.

I have tried
dd/mm/yyyy;;"-"
and this doesn't work.

Anyone with any suggestions ?

Anthony
 
The #VALUE! error you get is not a formatting issue. It is a formula issue.

It sounds like I made an incorrect assumption about the contents of your subsequent rows.

Suppose we look at the first row where data is not entered.

What are the contents of the cells in column B and Column L of that row?

We can modify the formula to show "-", but it will be much slower to troubleshoot without seeing your spreadsheet.

Can you try to upload again as Hui suggested?
 
Anthony,

As I look over the file you uploaded, I don't see the SUP or SEC or FE items that you referenced in your original post. Can you help me interpret what I'm seeing?

I understand that you want to build a formula for the Audit Due column that will display "Not Applicable" or Start Date + 45 days -- what criteria should be used to determine which of these to display?

What other columns did you want to work on?
 
Hi Eibe, I had to delete loads of information to get the file small enough to upload, so the items may have been removed.
Cell B6 will read either SUP, SEC or FE. If cell B6 reads SUP or SEC I would like H6 and K6 to read Not applicable. If B6 reads FE I would like it to take the date and add 45 days to it.

I already have the formula to do that in the spreadsheet, but the cell format is incorrect and reads an Error messaage when I copy it down the page. It should read a '-'.

Thanks in advance.


Anthony
 
See attached. I've put some test data (SEC,SUP,FE) in column B, and updated the formulas in H6 and K6. You can copy those formulas down as far as you want, and I think you will find "-" in the unused rows.

Anything remaining?
 

Attachments

  • Anthony1.xls
    48 KB · Views: 1
You must log in or register to reply here.
Back
Top