Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple if statements
I am using this formula:
=IF(B3=C16,C16,"")*AND(IF(C3=D16,D16,""))*AND(IF(D 3=E16,E16,"")) but am getting a value error What I am trying to do is if B3 = 2007 and C16 =2007 have 2007 displayed in A16. This is easy using =IF(B3=C16,C16,"") But I would also like to check if C3 = Q3 and D16 also equals Q3, I would like the result to be: 2007Q3 with no spaces In addition, in my second if statement if C3 does not equal D16, but my 3rd If statement where D3 does equal E16 I would like the result to be: 2007Accounting with no spaces if D3=Accounting and E16 also equals Accounting I am just using the words Accounting, 1007 and Q3 as examples as these variables will change. Any genius ideas out there? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple if statements
Let's look at: If B3 = 2007 and C16 =2007 have 2007 displayed in A16. This
is easy using =IF(B3=C16,C16,"") But I would also like to check if C3 = Q3 and D16 also equals Q3, I would like the result to be: 2007Q3 with no spaces =IF(AND(B3=2007,C16=2007,C3=Q3, D16=Q3), "2007"&TRIM(Q3), "something else") here I assume you mean the cell Q3 =IF(AND(B3=2007,C16=2007), IF(C3="Q3", D16="Q3"), "2007Q3, "not Q3"), "not 2007") here I assume the text "Q3" Now tell us what is to happen in the other cases -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Chris" wrote in message ... I am using this formula: =IF(B3=C16,C16,"")*AND(IF(C3=D16,D16,""))*AND(IF(D 3=E16,E16,"")) but am getting a value error What I am trying to do is if B3 = 2007 and C16 =2007 have 2007 displayed in A16. This is easy using =IF(B3=C16,C16,"") But I would also like to check if C3 = Q3 and D16 also equals Q3, I would like the result to be: 2007Q3 with no spaces In addition, in my second if statement if C3 does not equal D16, but my 3rd If statement where D3 does equal E16 I would like the result to be: 2007Accounting with no spaces if D3=Accounting and E16 also equals Accounting I am just using the words Accounting, 1007 and Q3 as examples as these variables will change. Any genius ideas out there? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple if statements
On Mar 18, 12:22*pm, "Bernard Liengme" wrote:
Let's look at: If B3 = 2007 and C16 =2007 have 2007 displayed in A16. This is easy using =IF(B3=C16,C16,"") But I would also like to check if C3 = Q3 and D16 also equals Q3, I would like the result to be: 2007Q3 with no spaces =IF(AND(B3=2007,C16=2007,C3=Q3, D16=Q3), "2007"&TRIM(Q3), "something else") here I assume you mean the cell Q3 =IF(AND(B3=2007,C16=2007), IF(C3="Q3", D16="Q3"), "2007Q3, "not Q3"), "not 2007") here I assume the text "Q3" Now tell us what is to happen in the other cases -- Bernard V Liengme Microsoft Excel MVPhttp://people.stfx.ca/bliengme remove caps from email "Chris" wrote in message ... I am using this formula: =IF(B3=C16,C16,"")*AND(IF(C3=D16,D16,""))*AND(IF(D 3=E16,E16,"")) but am getting a value error What I am trying to do is if B3 = 2007 and C16 =2007 have 2007 displayed in A16. This is easy using =IF(B3=C16,C16,"") But I would also like to check if C3 = Q3 and D16 also equals Q3, I would like the result to be: 2007Q3 with no spaces In addition, in my second if statement if C3 does not equal D16, but my 3rd If statement where D3 does equal E16 I would like the result to be: 2007Accounting with no spaces if D3=Accounting and E16 also equals Accounting I am just using the words Accounting, 1007 and Q3 as examples as these variables will change. Any genius ideas out there?- Hide quoted text - - Show quoted text - There are way too many variables to set up the formula like this. That is why I was referencing the cell instead of possible variables to be entered into the cell like "Q3" and "2007". The Q3 is not a cell, it is a possible variable. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple if statements
Throw away your *AND and replace with &
& is the operator for concatenation of strings. -- David Biddulph "Chris" wrote in message ... I am using this formula: =IF(B3=C16,C16,"")*AND(IF(C3=D16,D16,""))*AND(IF(D 3=E16,E16,"")) but am getting a value error What I am trying to do is if B3 = 2007 and C16 =2007 have 2007 displayed in A16. This is easy using =IF(B3=C16,C16,"") But I would also like to check if C3 = Q3 and D16 also equals Q3, I would like the result to be: 2007Q3 with no spaces In addition, in my second if statement if C3 does not equal D16, but my 3rd If statement where D3 does equal E16 I would like the result to be: 2007Accounting with no spaces if D3=Accounting and E16 also equals Accounting I am just using the words Accounting, 1007 and Q3 as examples as these variables will change. Any genius ideas out there? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple if statements
On Mar 18, 12:43*pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote: Throw away your *AND and replace with & & is the operator for concatenation of strings. -- David Biddulph "Chris" wrote in message ... I am using this formula: =IF(B3=C16,C16,"")*AND(IF(C3=D16,D16,""))*AND(IF(D 3=E16,E16,"")) but am getting a value error What I am trying to do is if B3 = 2007 and C16 =2007 have 2007 displayed in A16. This is easy using =IF(B3=C16,C16,"") But I would also like to check if C3 = Q3 and D16 also equals Q3, I would like the result to be: 2007Q3 with no spaces In addition, in my second if statement if C3 does not equal D16, but my 3rd If statement where D3 does equal E16 I would like the result to be: 2007Accounting with no spaces if D3=Accounting and E16 also equals Accounting I am just using the words Accounting, 1007 and Q3 as examples as these variables will change. Any genius ideas out there?- Hide quoted text - - Show quoted text - Perfecto! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple IF Statements | Excel Worksheet Functions | |||
Multiple IF statements looking up multiple ranges. | Excel Worksheet Functions | |||
Multiple if statements with multiple conditions | Excel Discussion (Misc queries) | |||
Multiple IF Statements | Excel Discussion (Misc queries) | |||
multiple IF statements | Excel Worksheet Functions |