multiple if statements

Microsoft Office 2007 home and student
July 21, 2010 at 08:24:10
Specs: Windows Vista
I am trying to create a formula with multiple IF statements. If the letter "a" appears on any of the pages, then I want it to show the results of the calculation

(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8)

If there is no "a" on any of the sheets, then I want the cell to remain blank.

The formula I have so far (which is definitely wrong) is:

=IF(((OR(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$T$28,10,)="a", (VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$T$50,10,)="a"), VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$T$35,10,)="a”, IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8),"")

Any help with this would be greatly appreciated.

Thanks.


See More: multiple if statements

Report •


#1
July 21, 2010 at 08:45:41
Try this:

=IF(OR(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$T$28,10,)="a",VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$T$50,10,)="a",VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$T$35,10,)="a”),B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8),"")

MIKE

http://www.skeptic.com/


Report •

#2
July 21, 2010 at 08:56:11
Hi,

To keep this simple, I have just used three worksheets Dec09, Jan10 & Feb10, and I used the same range on each - you can adjust the ranges as required.

The formula returns "X" if "a" is found in any of the ranges on the specified worksheets.

You can replace "X" with your formula.
Replace "Not found" with "" to display nothing

I used COUNTIF() rather than VLOOKUP()

VLOOKUP() only looks for the search item in the first column of the range specified, so VLOOKUP("a", G1:T30 .... only searches for "a" in cells G1 to G30
Also as you needed an exact match, VLOOKUP has to have the FALSE parameter, and if "a" is not found it returns an Error value, which can cause problems.

COUNTIF() just counts the number of instances of "a" in the range specified - and for your purpose any value above zero is what you need.

Here is the basic formula:
=IF(COUNTIF('December BANK 2009'!G1:T31,"=a")+COUNTIF('January BANK 2010'!G1:T31,"=a")+COUNTIF('February BANK 2010'!G1:T31,"=a")>0,"X","Not Found")

It adds the total number of a's found in the worksheets/ranges and If more than zero, it will use your formula.

Hope this helps.

Regards


Report •

#3
July 21, 2010 at 10:48:58
Mike - I tried the formula you wrote but it is coming up with an error still. It is reading my formula as "logical 3" still instead of the value if true....


Humar - I don't think this formula will give me the results I am seeking. Unless of course I am reading it wrong...

I only want the "a"'s in the specified column (column 10). "a" was used to mark a certain type of payment in the appropriate column in another workbook. If I let it pick up anything with an "a" I'll have figures I don't want.

I only want this formula to calculate if they have "a" in column 10 of that range.


Anita


Report •

Related Solutions

#4
July 21, 2010 at 10:52:49
Another note which may help:

The worksheets I am referring to (looking for the "a") - I was using the VLOOKUP function because I was searching for the contract number (found in cell C8) in the other sheet.

The sheet I am searching in...G column shows the contract number, so I need it to find the contract number in that column and then see if there is an "a" in the column mentioned (in this case, 10th column in that range).

Hope this helps a bit more.


Report •

#5
July 21, 2010 at 10:55:24
Hi,

Just change the formula to use column J (i.e. column 10)
=IF(COUNTIF('December BANK 2009'!J1:J31,"=a")+COUNTIF('January BANK 2010'!J1:J31,"=a")+COUNTIF('February BANK 2010'!J1:J31,"=a")>0,"X","Not Found")

Regards


Report •

#6
July 21, 2010 at 11:00:17
Hi,

Sorry, you've lost me.

Are you saying that you are not looking for "a" in column J (10th column) of the worksheets "January BANK 2010".

Can you be specific about the sequence and which workbooks and worksheets are being searched.

Regards


Report •

#7
July 21, 2010 at 11:20:57
Sorry, did not realize you had two IF statements, try this:

=IF(OR(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$T$28,10,)="a",VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$T$50,10,)="a",VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$T$35,10,)="a”),IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8),""),"")

As for Humars' suggestion, try this:

=IF(COUNTIF('December BANK 2009'!J1:J31,"=a")+COUNTIF('January BANK 2010'!J1:J31,"=a")+COUNTIF('February BANK 2010'!J1:J31,"=a")>0,IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8),""),"")

MIKE

http://www.skeptic.com/


Report •

#8
July 21, 2010 at 11:21:44
Thanks for your patience with this.

I started with workbook A which has a sheet for each month (i.e. December BANK 2009, January BANK 2010...etc.).

These sheets show detailed transactions by contract number. Column G has their contract number, column P has an "a" in it if they paid for it without financial assistance.

Then, in workbook B I calculate things based on dates, amounts, etc. so I know how much has been earned based on deposits. So in this workbook (B) I have their contract number in column C. In column Q (the one I am trying to create the formula for) I want it to total all of this contracts (see column C) earned amounts (my equation) but only if they selected "a" in column P (of workbook B).

I have a working equation for referring to just one sheet of workbook B. It is

=IF(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$T$28,10,)="a",IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8))),"")

But for each sheet I add, I need to search one extra sheet in workbook B (for each additional month). It is necessary for me to still search the previous months, because they may have paid previously.

I hope this information helps at least a little...


Report •

#9
July 21, 2010 at 11:50:20
Hi,

Try this - for just three of the monthly worksheets.

The formula was in cell Q8, and takes the contract number from C8 (i.e. on the same row as the formula).

=IF(OR(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$T$28,10,)="a",VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$T$28,10,)="a",VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$T$28,10,)="a"),IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8))),"")

Regards


Report •

#10
July 21, 2010 at 12:20:16
Hi,

The difference between my suggested formula and yours is that you have bracketed the VLOOKUP formulas within the OR statement, and the OR statement has not enclosed the three VLOOKUPS in a single set of brackets.

You just need OR(test1,test2,test3)
Each VLOOKUP can be used like this:
VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$T$28,10,)="a"
In this case the result of the VLOOKUP() is compared to the value "a" and returns TRUE or FALSE. OR is true if any one of the VLOOKUPS equals "a"

I note that you have not used the last parameter in VLOOKUP, after the 10. This means that VLOOKUP will return a closest match (default for the parameter is TRUE). Your VLOOKUP is actually returning something else when "a" is not present, but as you compare it to "a" the formula still works.

The lookup range $G$6:$T$28 technically only needs to be $G$6:$P$28 because column P is the 10th column from the start of the range - In VLOOKUP, column G (in this case), is column 1, so P is column 10

Regards


Report •

#11
July 21, 2010 at 12:53:25
Thanks MIke, but it still shows the same error message.

Report •

#12
July 21, 2010 at 12:54:27
Humar, I am trying your recent suggestion now. Will reply momentarily

Report •

#13
July 22, 2010 at 13:24:28
When I try the formula, it accepts it but the error message #N/A shows up.

I traced the formula and there should actually be an amount in this cell because there is an "a" on the January and February sheet for this contract number (columnn C)

Is there something simple I'm missing here maybe?


Report •

#14
July 22, 2010 at 14:22:02
Hi,

If the contract number does not appear in every month sheet, VLOOKUP will rreturn the #NA error

In each Vlookup add TRUE after the last comma and before the closing bracket.
VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$P$28,10,TRUE)="a"

However VLOOKUP with the true parameter requires that all values are in order in the column it searches - column G on each sheet in this case.

Vlookup with FALSE only finds an exact match, and the values (contract numbers) do not have to be in numerical order - but - it returns an #NA error if the value is not present.

If contract numbers are in numerical order, you still have a problem because VLOOKUP will return the next largest contract number.

In your case using FALSE is safer, but if you don't have all contract numbers on every month sheet you will get the #NA error.

It is possible to trap the #NA error - with an addition to each VLOOKUP.

Let me know what the situation is - do all sheets have all contract numbers & are the contract numbers in order.

Regards


Report •

#15
July 23, 2010 at 13:30:10
Each sheet has the contract numbers listed for each transaction. They are not, however, in numerical order. Also, a "contract" may be listed more than once if they have made more than one payment. Each time they make a payment, the amount is listed in one column and the a (formated to show as a check mark) is placed in whichever column explains how they received the funding. (ie. self, employer, etc.)

Report •

#16
July 23, 2010 at 14:55:05
Hi,

You can try this:
=IF(OR(IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$G$28,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$P$28,10,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$G$28,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$P$28,10,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$G$28,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$P$28,10,FALSE)="a")),IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8))),"")

It uses the FALSE parameter for VLOOKUP, which ensures only exact matches, and it uses the ISNA function to catch the cases when VLOOKUP does not find a match. Also using the FALSE parameter means that contract numbers do not have to be in order.

I note that you say that you may have more than one instance of a contract number.

VLOOKUP will only find the first instance. If "a" is only present on say a second instance of the contract number VLOOKUP will never return that.

In view of the current complexity I wonder if there is a better way to approach this issue.

It might be better to include some additional processing in the OTTA YEAR END ... workbook. Perhaps an additional page that captures the contract numbers from all month sheets and includes the "a" status. Then the other worksheet only has to do a lookup in one place instead of in all 12 month sheets.

Regards


Report •

#17
July 26, 2010 at 11:23:18
That formula seems to work great for this cell.

You are absolutely correct about adding another sheet for contract numbers. That would be far better. Unfortunately, there are some things in place here that some people do not wish to change. It is a great suggestion and I will work on getting approval and adding that page.

Fo now though, it is the more complicated route...

I entered that formula (with a couple of corrections to the ranges). It ended up being:
=IF(OR(IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$G$28,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$P$28,10,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$G$50,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$P$50,10,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$G$35,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$P$35,10,FALSE)="a")),IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8))),"")

Now, I need to have a similar formula to this, just located over in a different column and referring to column 11 on those sheets instead of 10. I tried adjusting the formula as:
=IF(OR(IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$G$28,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$P$28,11,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$G$50,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$P$50,11,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$G$35,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$P$35,11,FALSE)="a")),IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8))),"")

but it just gives me an error message of "#REF!"

What do I need to do differently? I fear there is something obvious that I do not see...


Report •

#18
July 26, 2010 at 12:14:33
Hi,

At a quick glance, I think that the problem is the range in the second VLOOKUP() in each pair:
$G$6:$P$50,11,FALSE

The offset is 11, but the range only covers 10 columns (G to P)
So now you need to increase the range to 11 columns:
$G$6:$Q$50,11,FALSE

Regards


Report •

#19
July 26, 2010 at 14:18:24
Thank you so much. That is exactly what it was.

So far, this formula is working just as I had hoped it would. You are nothing short of amazing in my eyes. I will continue to plug in all data and see if it continues to work without problem.

Thank you ever so much for all of your time with this.


Report •

#20
July 26, 2010 at 16:23:07
Hi,

Thanks for the response and your kind words.

I wish you well with your endeavor.

Regards

Humar


Report •

#21
August 16, 2010 at 07:00:01
Hi again Humar,


This formula works great, but I have just been advised that if cell A23 says "C", "D Straight Truck", "DZ FF", "Z" or "Other" (and also has the letter "a" appearing on the sheets mentioned), then it needs to equal what is shown in cell I23. If A23 says anything else, then the formula needs to be as it was.

I have been trying to adjust it and this is what I have so far:

=IF(OR(A23=“C”,A23=“D Straight Truck”,A23=“DZ FF”,A23=“Z”,A23=“Other”), IF(OR(IF(ISNA(VLOOKUP(C23,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$7:$G$110,1,FALSE)),FALSE,VLOOKUP(C23,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$7:$T$110,10,FALSE)="a"),IF(ISNA(VLOOKUP(C23,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$7:$G$110,1,FALSE)),FALSE,VLOOKUP(C23,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$7:$T$110,10,FALSE)="a")),I23, IF(OR(IF(ISNA(VLOOKUP(C23,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$7:$G$110,1,FALSE)),FALSE,VLOOKUP(C23,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$7:$T$110,10,FALSE)="a"),IF(ISNA(VLOOKUP(C23,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$7:$G$110,1,FALSE)),FALSE,VLOOKUP(C23,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$7:$T$110,10,FALSE)="a")),IF(B23/N23*O23-P23-(B23-J23)>J23,J23,MAX(0,B23/N23*O23-P23-(B23-J23))),"")

Excel is telling me I am missing a parenthesis. Also, I am not sure that this is the best way to complete the formula.

I would love it if you could take a look for me.


Report •

#22
August 16, 2010 at 07:59:17
Hi,

From what I think you require :)
You have two groups of OR statements -
One is the original OR which tests for "a" on one of the worksheets and
the Other is a new test of possible values in cell A23 ("C", "D" etc.)

As the new OR statements have to be false i.e., none of them are true when one of the original OR statements for "a" is true, then we negate the first OR, so NOT(OR(,,,))

Then we can AND the NOT(OR() with the original OR
so if none of the new OR tests is true AND one of the original OR statements is true, we use the original formula

In the full original formula if the original OR tests for "a" were all false we returned ""
Now when either one of the new tests is true or none of the old tests for "a" is true, we return the value in I23.

Please try this:
=IF(AND(NOT(OR(A23="C",A23="D Straight Truck",A23="DZ FF",A23="Z",A23="Other")),OR(IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$G$28,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$Q$28,11,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$G$50,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$Q$50,11,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$G$35,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$Q$35,11,FALSE)="a"))),IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8))),I23)

It breaks down like this:
=IF(
the if test:
AND(
the first of two And's (a Not/Or):
NOT(OR(A23="C",A23="D Straight Truck",A23="DZ FF",A23="Z",A23="Other")),
the second of two And's (an Or consisting of a series of Vlookups for the value "a"):
OR(IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$G$28,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$Q$28,11,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$G$50,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$Q$50,11,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$G$35,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$Q$35,11,FALSE)="a")
) end of And
) end of If test
Result if true:
,IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8))),
result if false
I23)

In summary:
IF(AND(NOT(OR(tests of A23)),OR(vlookups for "a")),result if true, result if false)

Regards

Humar


Report •

#23
August 16, 2010 at 08:16:17
We almost have it. Whether A23 is true or false, I still need vlookup to determine if there will be a value in that cell. The value in A23 will determine what the result will be (I23 or formula)

If A23 tests are true AND if vlookups for "a", then will be I23.

If A23 tests are false BUT vllookups for "a" are true, then the original formula will run:
IF(B23/N23*O23-P23-(B23-J23)>J23,J23,MAX(0,B23/N23*O23-P23-(B23-J23))),"")

If vlookups for "a" are false, then the cell needs to remain blank.


Report •

#24
August 17, 2010 at 05:54:03
Hi,

Here we go again!

The formual starts by testiing the worksheets for "a"
If false it returns a blank cell ""

If true it tests A23
If A23 contains one of the values C, D etc, the result returned is I23
If A23 does not contain one of the values C, D etc the original formula is used.

Here is the formula:
=IF(OR(IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$G$28,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$Q$28,11,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$G$50,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$Q$50,11,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$G$35,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$Q$35,11,FALSE)="a")),IF(OR(A23="C",A23="D Straight Truck",A23="DZ FF",A23="Z",A23="Other"),I23,IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8)))),"")

The structure is:

=IF(OR(tests for "a"),
if tests for "a" are true then tests A23
	IF(OR(tests A23),
	if A23 test is true then 
		returns I23,
	if A23 test is false then 
		runs the original formula:
	IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8))))
If tests for "a" are false then return blank:
,"")

Regards


Report •

#25
August 20, 2010 at 13:44:06
Thanks Humar!

That worked fabulous. :-)


Report •

#26
August 20, 2010 at 15:42:17
Hi,

So glad to hear it worked - I had started to have nightmares where I was searching for missing parentheses ...

Have a great day

Regards

Humar


Report •

#27
August 23, 2010 at 08:52:32
Those are never good dreams Humar! :-)

I do have another question regarding the saving of this workbook(s).

Should I submit a separate question or can I just ask you here?


Report •

#28
August 23, 2010 at 09:08:58
Hi,

Please start a new post as it's a different issue.

Regards


Report •

Ask Question