| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
nested IF(ISERROR()) statement
I get an 'you've entered too many arguements' error message with my formula
below. What do I need to do to get it to work? I have 2 tables, PR and nonPR, and I want to add values together where the left most strings match in each. If only one table contains the string then take that value, if neither do then return a blank. =IF(ISERROR(VLOOKUP(K16,pr,3,FALSE)),if(iserror(VL OOKUP(K16,nonpr,3,FALSE)),"",VLOOKUP(K16,nonpr,3,F ALSE)*1000,if(iserror(VLOOKUP(K16,nonpr,3,FALSE)), VLOOKUP(K16,pr,3,FALSE),VLOOKUP(K16,pr,3,FALSE)+(V LOOKUP(K16,nonpr,3,FALSE)*1000)))) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
nested IF(ISERROR()) statement
Put the VLOOKUP formula in their own cells and reference these cells.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DC" wrote in message ... I get an 'you've entered too many arguements' error message with my formula below. What do I need to do to get it to work? I have 2 tables, PR and nonPR, and I want to add values together where the left most strings match in each. If only one table contains the string then take that value, if neither do then return a blank. =IF(ISERROR(VLOOKUP(K16,pr,3,FALSE)),if(iserror(VL OOKUP(K16,nonpr,3,FALSE)),"",VLOOKUP(K16,nonpr,3,F ALSE)*1000,if(iserror(VLOOKUP(K16,nonpr,3,FALSE)), VLOOKUP(K16,pr,3,FALSE),VLOOKUP(K16,pr,3,FALSE)+(V LOOKUP(K16,nonpr,3,FALSE)*1000)))) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
nested IF(ISERROR()) statement
Thanks Bob, but I still get the 'too many arguments' message.
"Bob Phillips" wrote: Put the VLOOKUP formula in their own cells and reference these cells. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DC" wrote in message ... I get an 'you've entered too many arguements' error message with my formula below. What do I need to do to get it to work? I have 2 tables, PR and nonPR, and I want to add values together where the left most strings match in each. If only one table contains the string then take that value, if neither do then return a blank. =IF(ISERROR(VLOOKUP(K16,pr,3,FALSE)),if(iserror(VL OOKUP(K16,nonpr,3,FALSE)),"",VLOOKUP(K16,nonpr,3,F ALSE)*1000,if(iserror(VLOOKUP(K16,nonpr,3,FALSE)), VLOOKUP(K16,pr,3,FALSE),VLOOKUP(K16,pr,3,FALSE)+(V LOOKUP(K16,nonpr,3,FALSE)*1000)))) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
nested IF(ISERROR()) statement
Your problem is after the first 1000, as you are trying to have
another parameter after the false argument of the IF. Try this amendment: =IF(ISERROR(VLOOKUP(K16,pr,3,FALSE)),if(iserror(VL OOKUP(K16,nonpr, 3,FALSE)),"",VLOOKUP(K16,nonpr, 3,FALSE)*1000),if(iserror(VLOOKUP(K16,nonpr,3,FALS E)),*VLOOKUP(K16,pr, 3,FALSE),VLOOKUP(K16,pr,3,FALSE)+VLOOKUP(K16,nonpr ,3,FALSE)**1000)) Hope this helps (and that I've counted all the brackets correctly) Pete On May 30, 3:35 pm, DC wrote: Thanks Bob, but I still get the 'too many arguments' message. "Bob Phillips" wrote: Put the VLOOKUP formula in their own cells and reference these cells. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DC" wrote in message ... I get an 'you've entered too many arguements' error message with my formula below. What do I need to do to get it to work? I have 2 tables, PR and nonPR, and I want to add values together where the left most strings match in each. If only one table contains the string then take that value, if neither do then return a blank. =IF(ISERROR(VLOOKUP(K16,pr,3,FALSE)),if(iserror(VL OOKUP(K16,nonpr,3,FALSE))*,"",VLOOKUP(K16,nonpr,3, FALSE)*1000,if(iserror(VLOOKUP(K16,nonpr,3,FALSE)) ,*VLOOKUP(K16,pr,3,FALSE),VLOOKUP(K16,pr,3,FALSE)+ (VLOOKUP(K16,nonpr,3,FALSE)**1000))))- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
nested IF(ISERROR()) statement
Excellent Pete, well spotted
not sure you meant to include the negative signs though. Many thanks "Pete_UK" wrote: Your problem is after the first 1000, as you are trying to have another parameter after the false argument of the IF. Try this amendment: =IF(ISERROR(VLOOKUP(K16,pr,3,FALSE)),if(iserror(VL OOKUP(K16,nonpr, 3,FALSE)),"",VLOOKUP(K16,nonpr, 3,FALSE)*1000),if(iserror(VLOOKUP(K16,nonpr,3,FALS E)),-VLOOKUP(K16,pr, 3,FALSE),VLOOKUP(K16,pr,3,FALSE)+VLOOKUP(K16,nonpr ,3,FALSE)-*1000)) Hope this helps (and that I've counted all the brackets correctly) Pete On May 30, 3:35 pm, DC wrote: Thanks Bob, but I still get the 'too many arguments' message. "Bob Phillips" wrote: Put the VLOOKUP formula in their own cells and reference these cells. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DC" wrote in message ... I get an 'you've entered too many arguements' error message with my formula below. What do I need to do to get it to work? I have 2 tables, PR and nonPR, and I want to add values together where the left most strings match in each. If only one table contains the string then take that value, if neither do then return a blank. =IF(ISERROR(VLOOKUP(K16,pr,3,FALSE)),if(iserror(VL OOKUP(K16,nonpr,3,FALSE))-,"",VLOOKUP(K16,nonpr,3,FALSE)*1000,if(iserror(VLO OKUP(K16,nonpr,3,FALSE)),-VLOOKUP(K16,pr,3,FALSE),VLOOKUP(K16,pr,3,FALSE)+(V LOOKUP(K16,nonpr,3,FALSE)-*1000))))- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
nested IF(ISERROR()) statement
Make sure that you adjust Pete's formula to multiply the PR range by 1000 if
the value is found in *both* ranges. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "DC" wrote in message ... Excellent Pete, well spotted not sure you meant to include the negative signs though. Many thanks "Pete_UK" wrote: Your problem is after the first 1000, as you are trying to have another parameter after the false argument of the IF. Try this amendment: =IF(ISERROR(VLOOKUP(K16,pr,3,FALSE)),if(iserror(VL OOKUP(K16,nonpr, 3,FALSE)),"",VLOOKUP(K16,nonpr, 3,FALSE)*1000),if(iserror(VLOOKUP(K16,nonpr,3,FALS E)),-VLOOKUP(K16,pr, 3,FALSE),VLOOKUP(K16,pr,3,FALSE)+VLOOKUP(K16,nonpr ,3,FALSE)-*1000)) Hope this helps (and that I've counted all the brackets correctly) Pete On May 30, 3:35 pm, DC wrote: Thanks Bob, but I still get the 'too many arguments' message. "Bob Phillips" wrote: Put the VLOOKUP formula in their own cells and reference these cells. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DC" wrote in message ... I get an 'you've entered too many arguements' error message with my formula below. What do I need to do to get it to work? I have 2 tables, PR and nonPR, and I want to add values together where the left most strings match in each. If only one table contains the string then take that value, if neither do then return a blank. =IF(ISERROR(VLOOKUP(K16,pr,3,FALSE)),if(iserror(VL OOKUP(K16,nonpr,3,FALSE))-,"",VLOOKUP(K16,nonpr,3,FALSE)*1000,if(iserror(VLO OKUP(K16,nonpr,3,FALSE)),-VLOOKUP(K16,pr,3,FALSE),VLOOKUP(K16,pr,3,FALSE)+(V LOOKUP(K16,nonpr,3,FALSE)-*1000))))- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
nested IF(ISERROR()) statement
The negative signs are to do with word-wrap on the newsgroups - no,
they should not be included (and they don't show on Google Groups). Glad it worked for you - thanks for feeding back. Pete On May 30, 4:40 pm, DC wrote: Excellent Pete, well spotted not sure you meant to include the negative signs though. Many thanks "Pete_UK" wrote: Your problem is after the first 1000, as you are trying to have another parameter after the false argument of the IF. Try this amendment: =IF(ISERROR(VLOOKUP(K16,pr,3,FALSE)),if(iserror(VL OOKUP(K16,nonpr, 3,FALSE)),"",VLOOKUP(K16,nonpr, 3,FALSE)*1000),if(iserror(VLOOKUP(K16,nonpr,3,FALS E)),-VLOOKUP(K16,pr, 3,FALSE),VLOOKUP(K16,pr,3,FALSE)+VLOOKUP(K16,nonpr ,3,FALSE)-*1000)) Hope this helps (and that I've counted all the brackets correctly) Pete On May 30, 3:35 pm, DC wrote: Thanks Bob, but I still get the 'too many arguments' message. "Bob Phillips" wrote: Put the VLOOKUP formula in their own cells and reference these cells. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DC" wrote in message ... I get an 'you've entered too many arguements' error message with my formula below. What do I need to do to get it to work? I have 2 tables, PR and nonPR, and I want to add values together where the left most strings match in each. If only one table contains the string then take that value, if neither do then return a blank. =IF(ISERROR(VLOOKUP(K16,pr,3,FALSE)),if(iserror(VL OOKUP(K16,nonpr,3,FALSE))*-,"",VLOOKUP(K16,nonpr,3,FALSE)*1000,if(iserror(VLO OKUP(K16,nonpr,3,FALSE))*,-VLOOKUP(K16,pr,3,FALSE),VLOOKUP(K16,pr,3,FALSE)+(V LOOKUP(K16,nonpr,3,FALS*E)-*1000))))- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
nested IF(ISERROR()) statement
See if this works for you:
=IF(ISNA(VLOOKUP(K16,PR,3,0)),IF(ISNA(VLOOKUP(K16, nonPR,3,0)),"",VLOOKUP(K16,nonPR,3,0)*1000),IF(ISN A(VLOOKUP(K16,nonPR,3,0)),VLOOKUP(K16,PR,3,0)*1000 ,(VLOOKUP(K16,PR,3,0)+VLOOKUP(K16,nonPR,3,0))*1000 )) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "DC" wrote in message ... Thanks Bob, but I still get the 'too many arguments' message. "Bob Phillips" wrote: Put the VLOOKUP formula in their own cells and reference these cells. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DC" wrote in message ... I get an 'you've entered too many arguements' error message with my formula below. What do I need to do to get it to work? I have 2 tables, PR and nonPR, and I want to add values together where the left most strings match in each. If only one table contains the string then take that value, if neither do then return a blank. =IF(ISERROR(VLOOKUP(K16,pr,3,FALSE)),if(iserror(VL OOKUP(K16,nonpr,3,FALSE)),"",VLOOKUP(K16,nonpr,3,F ALSE)*1000,if(iserror(VLOOKUP(K16,nonpr,3,FALSE)), VLOOKUP(K16,pr,3,FALSE),VLOOKUP(K16,pr,3,FALSE)+(V LOOKUP(K16,nonpr,3,FALSE)*1000)))) |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
nested IF(ISERROR()) statement
I'm confused here before anyone else in the thread is..
I'm looking for the FIRST action to be taken if the FIRST IF is true... =IF(ISERROR(VLOOKUP(K16,pr,3,FALSE)), What to do if true goes here, But there is nothing spelled out to do !! (then and only then) should come the (creating a NESTED IF STATEMENT) if(iserror(VLOOKUP(K16,nonpr,3,FALSE)),"" <<< Ok, here I see the action to be taken if the second If statement is TRUE.. Does anyone else see my reason for confusion? Thanks in advance. Jim "DC" wrote in message : I get an 'you've entered too many arguements' error message with my formula below. What do I need to do to get it to work? I have 2 tables, PR and nonPR, and I want to add values together where the left most strings match in each. If only one table contains the string then take that value, if neither do then return a blank. =IF(ISERROR(VLOOKUP(K16,pr,3,FALSE)),if(iserror(VL OOKUP(K16,nonpr,3,FALSE)),"",VLOOKUP(K16,nonpr,3,F ALSE)*1000,if(iserror(VLOOKUP(K16,nonpr,3,FALSE)), VLOOKUP(K16,pr,3,FALSE),VLOOKUP(K16,pr,3,FALSE)+(V LOOKUP(K16,nonpr,3,FALSE)*1000)))) |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
nested IF(ISERROR()) statement
Ok, reconsidering things here, I guess you can just as easily have a
nested if statement which says... If(TRUE,If(),otherwise whatever) -- If right, then Sorry for posting unnecessarily. "JMay" wrote in message : I'm confused here before anyone else in the thread is.. I'm looking for the FIRST action to be taken if the FIRST IF is true... =IF(ISERROR(VLOOKUP(K16,pr,3,FALSE)), What to do if true goes here, But there is nothing spelled out to do !! (then and only then) should come the (creating a NESTED IF STATEMENT) if(iserror(VLOOKUP(K16,nonpr,3,FALSE)),"" <<< Ok, here I see the action to be taken if the second If statement is TRUE.. Does anyone else see my reason for confusion? Thanks in advance. Jim "DC" wrote in message : I get an 'you've entered too many arguements' error message with my formula below. What do I need to do to get it to work? I have 2 tables, PR and nonPR, and I want to add values together where the left most strings match in each. If only one table contains the string then take that value, if neither do then return a blank. =IF(ISERROR(VLOOKUP(K16,pr,3,FALSE)),if(iserror(VL OOKUP(K16,nonpr,3,FALSE)),"",VLOOKUP(K16,nonpr,3,F ALSE)*1000,if(iserror(VLOOKUP(K16,nonpr,3,FALSE)), VLOOKUP(K16,pr,3,FALSE),VLOOKUP(K16,pr,3,FALSE)+(V LOOKUP(K16,nonpr,3,FALSE)*1000)))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF STATEMENT - NESTED | Excel Worksheet Functions | |||
| Iserror nested within a conditional vlookup | Excel Worksheet Functions | |||
| Nested IF Statement | Excel Discussion (Misc queries) | |||
| Nested IF/AND Statement | Excel Discussion (Misc queries) | |||
| Nested If statement | Excel Worksheet Functions | |||
Hybrid Mode
