if(and formula

davidroy

I'm having trouble with a "if(and" formula.

=IF(AND({KCPL Hours_ASSIGNED TO} = [Assigned To]@row, {KCPL Hours_TASK} = [Task Name]@row, {KCPL Hours_Project Number} = [Task Name]6), "1", "2")

But I get #INVALID OPERATION error message.

Any help would greatly be appreciated.

Thanks,

David

Paul Newcome

Are your ranges all single cells?

davidroy

No, Columns

David

davidroy

In running a test to see if it worked I change it to:

=IF(AND([Task Name]@row = INDEX({KCPL Hours_TASK}, MATCH([Task Name]@row, {KCPL Hours_TASK})), [Task Name]6 = INDEX({KCPL Hours _PROJECT_NUMBER}, MATCH([Task Name]6, {KCPL Hours _PROJECT_NUMBER})), "1", "2")))

Now get UNPARSEABLE

David

Paul Newcome

This second formula looks a little better. Your first was was essentially saying "if the entire column equals x". This newer formula is pulling an individual cell to compare to an individual cell. There are just a couple of suggestions I have, and there is one issue that is causing your error.

 

1. The issue causing the error is that you need to remove two of the closing parenthesis from the end of the formula and add one after the last INDEX/MATCH to close out your AND statement.

NUMBER})), "1", "2")))

should be

NUMBER}))), "1", "2")

.

2. I suggest specifying 0 (zero) as the sorting type in your MATCH statement. While it may not be necessary, I personally have found that I get the most consistent and accurate results using it.

.

3. If you are planning on using the 1 and 2 as actual numbers later on, you would want to remove the quotes from around them. Using the quotes turns it into a text string that just looks like a number but can't be used as a number. Removing the quotes allows you to use it as a number in future calculations.

(Just an observation here) ^

davidroy

Great that worked. Thanks.

Now I'm moving on to the next part.

I'm using the following formula and it works with on exception.

=IF(AND([Task Name]@row = INDEX({KCPL Hours_TASK}, MATCH([Task Name]@row, {KCPL Hours_TASK})), [Task Name]6 = INDEX({KCPL Hours _PROJECT_NUMBER}, MATCH([Task Name]6, {KCPL Hours _PROJECT_NUMBER})), [Assigned To]@row = INDEX({KCPL Hours_ASSIGNED TO}, MATCH([Assigned To]@row, {KCPL Hours_ASSIGNED TO}))), INDEX({Time & Material Rates_UNIT PRICE}, MATCH([Task Name]@row, {Time & Material Rates_PCL}, 0)) * INDEX({KCPL Hours_NUMBER OF HOURS}, MATCH([Assigned To]@row, {KCPL Hours_ASSIGNED TO}, 0)), 0)

The above gives me the correct amount if the AND is met, but where there is a problem it shows a #NO MATCH if the AND fails.  That fine except I need for it to be a zero (0).

I added an IFERROR

=IFERROR(IF(AND([Task Name]@row = INDEX({KCPL Hours_TASK}, MATCH([Task Name]@row, {KCPL Hours_TASK})), [Task Name]6 = INDEX({KCPL Hours _PROJECT_NUMBER}, MATCH([Task Name]6, {KCPL Hours _PROJECT_NUMBER})),[Assigned To]@row = INDEX({KCPL Hours_ASSIGNED TO}, MATCH([Assigned To]@row , {KCPL Hours_ASSIGNED TO}))), 

INDEX({Time & Material Rates_UNIT PRICE}, MATCH([Task Name]@row, {Time & Material Rates_PCL}, 0)) * INDEX({KCPL Hours_NUMBER OF HOURS}, MATCH([Assigned To]@row, {KCPL Hours_ASSIGNED TO}, 0)), 0),0)

but with the AND matching all I a zero instead of an amount.  I know I have something wrong, but since I'm new to this cannot see it.  Thanks for your help!

David

Paul Newcome

See if this works...

 

=IFERROR(IF(AND([Task Name]@row = INDEX({KCPL Hours_TASK}, MATCH([Task Name]@row, {KCPL Hours_TASK})), [Task Name]6 = INDEX({KCPL Hours _PROJECT_NUMBER}, MATCH([Task Name]6, {KCPL Hours _PROJECT_NUMBER})),[Assigned To]@row = INDEX({KCPL Hours_ASSIGNED TO}, MATCH([Assigned To]@row , {KCPL Hours_ASSIGNED TO}))), INDEX({Time & Material Rates_UNIT PRICE}, MATCH([Task Name]@row, {Time & Material Rates_PCL}, 0)) * INDEX({KCPL Hours_NUMBER OF HOURS}, MATCH([Assigned To]@row, {KCPL Hours_ASSIGNED TO}, 0))),0)

davidroy

Not quite.  The cell I put it should fail the AND and show zero. 

But it shows a dollar amount.

Almost there.

David

 

PS: Do you require additional info?

Paul Newcome

Some screenshots would be really helpful.

 

Lets break it down and do some trouble shooting.

 

Take each portion of the AND statement and put it in it's own IF statement. Keep the return values simple. Something like "Works" for the if true portion and "Check" for the else section.

 

Put in dummy data so that they should all be true.

davidroy

I got it figured out (see formula below)

=IFERROR(IF(AND([Task Name]@row = INDEX({KCPL Hours_TASK}, MATCH([Task Name]@row, {KCPL Hours_TASK})), [Task Name]5 = INDEX({KCPL Hours _PROJECT_NUMBER}, MATCH([Task Name]5, {KCPL Hours _PROJECT_NUMBER})), [Assigned To]@row = INDEX({KCPL Hours_ASSIGNED TO}, MATCH([Assigned To]@row, {KCPL Hours_ASSIGNED TO}))), INDEX({Time & Material Rates_UNIT PRICE}, MATCH([Task Name]@row, {Time & Material Rates_PCL}, 0)) * INDEX({KCPL Hours_NUMBER OF HOURS}, MATCH([Assigned To]@row, {KCPL Hours_ASSIGNED TO}, 0)), 0), 0)

Seems I had a couple extra ")" in the formula.

Thanks again,

David

Paul Newcome

Glad you got it working. 

 

Those parenthesis can get a little messy.