Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Establish formula for multiple logical_tests
I got the below data that I need to find the ultimate result after comparing
multiple logical_tests. I had tried using IF, OR, AND, MATCH command, but to no avail. Hope some of you can give me some suggestion as to what other command that I should be using. The data as follows : ## QUOTE ## Transactional size limit = USD1 mio ----------------------------------------- Buyer1 Transactional size limit = USD750K ---------------------------------------- Buyer2 Buyer3 Transactional size limit = USD500K ---------------------------------------- Buyer4 Buyer5 Buyer6 Remark 1 : The above shows the individual buyer being categorise with the different transactional size limit. Remark 2 : Only the listed buyer names are allow ## UNQUOTE ## To start, I will input 2 information in the spreadsheet i.e. : 1. Buyer name 2. Application amount Now I need to formulate a formula which will : 1. Check the buyer name is match 2. If buyer name not match, to highlight "Buyer not in approved list" 2. If match, then proceed to check the application amount i.e. whether less or equal to the allocated individual transactional limit 3. If amount larger than the allocated transactional limit, to highlight as "Amount Excess", else, to highlight as "Transactional amount within" Based on the above, I hope somebody can help me. This is really pain in the neck for me, cause had tried many ways to do it. Unless, I can formulate a formula, else, I will need to do manual checking everytime I doing the transaction, and hopefully not oversight anything in the process. Lastly, sorry for the lengthy data. Many thanks in advanced Stephen Lim |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Establish formula for multiple logical_tests
Hi Stephen, Try entering this in cell C1, =IF(ISNA(VLOOKUP(*$A1*,-$D$1:$E$6-,2,FALSE)),"Buyer not in approved list",IF(*$B1*VLOOKUP(*$A1*,-$D$1:$E$6-,2,FALSE),"Amount Excess","Transactional amount within")) Another method which can be used for visual effect is to use conditional formatting to colour the input cells red if either of the criteria are not met. To set this up, select "conditional formatting" from the Format menu, change the dropdown box to "formula is", enter the following formula, click format & then patterns, choose a colour eg red and "ok" all the way out. =IF(ISNA(VLOOKUP($A1,$D$1:$E$6,2,FALSE)),"TRUE",IF ($B1VLOOKUP($A1,$D$1:$E$6,2,FALSE),"TRUE","FALSE" )) Both the above methods require that: *The input cells are, cell A1 containing the buyer's name & cell B1 containing the amount (shown above in bold). *The lookup table of the list of buyers & their transactional size limits are in columns D & E (resp., shown in italics above). hth Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=533361 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Establish formula for multiple logical_tests
Hi Rob,
Thanks a lot for the solution given. It really fits well into my problem. However, as Im quite new to the functions used in your equation, I do need to find some time to get the logic behind. Nevertheless, I still have some question which I hope you can enlighten me. Firstly, just imagine my spreadsheet currently have 2 amount columns i.e. B1 and F1, both mutually exclusive. I had tried to edit part of your equation, which is as follows: IF(OR($B1, $F1) VLOOKUP(*$A1*,-$D$1:$E$6-,2,FALSE)," But error occurred i.e. my spreadsheet keep showing remark Amount Excess. Why this happen? Secondly, your equation contain operator * and respectively. So, may I know what is it? And should I manually key into the equation or is there ant shortcut key? Lastly, your equation show integer 2. Does this represent the 2 columns i.e. -$D$1:$E$6-. Your reply is greatly appreciated. Best regards Stephen Lim "broro183" wrote: Hi Stephen, Try entering this in cell C1, =IF(ISNA(VLOOKUP(*$A1*,-$D$1:$E$6-,2,FALSE)),"Buyer not in approved list",IF(*$B1*VLOOKUP(*$A1*,-$D$1:$E$6-,2,FALSE),"Amount Excess","Transactional amount within")) Another method which can be used for visual effect is to use conditional formatting to colour the input cells red if either of the criteria are not met. To set this up, select "conditional formatting" from the Format menu, change the dropdown box to "formula is", enter the following formula, click format & then patterns, choose a colour eg red and "ok" all the way out. =IF(ISNA(VLOOKUP($A1,$D$1:$E$6,2,FALSE)),"TRUE",IF ($B1VLOOKUP($A1,$D$1:$E$6,2,FALSE),"TRUE","FALSE" )) Both the above methods require that: *The input cells are, cell A1 containing the buyer's name & cell B1 containing the amount (shown above in bold). *The lookup table of the list of buyers & their transactional size limits are in columns D & E (resp., shown in italics above). hth Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=533361 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Establish formula for multiple logical_tests
Hi Stephen, I'm pleased I'm on the right track. I'll start with this one as it will make my other responses more understandable: "Secondly, your equation contain operator * and respectively. So, may I know what is it? And should I manually key into the equation or is there ant shortcut key?" These operators should not be in the equation - sorry for the problems with the equations, it looks like the formatting (bold & italics) I used when I answered your post through excelforum.com has been messed up, the two equations should have been: =IF(ISNA(VLOOKUP($A1,$D$1:$E$6,2,FALSE)),"Buyer not in approved list",IF($B1VLOOKUP($A1,$D$1:$E$6,2,FALSE),"Amoun t Excess","Transactional amount within")) =IF(ISNA(VLOOKUP($A1,$D$1:$E$6,2,FALSE)),"TRUE",IF ($B1VLOOKUP($A1,$D$1:$E$6,2,FALSE),"TRUE","FALSE" )) "Firstly, just imagine my spreadsheet currently have 2 amount columns i.e. B1 and F1, both mutually exclusive. I had tried to edit part of your equation, which is as follows: IF(OR($B1, $F1) VLOOKUP(*$A1*,-$D$1:$E$6-,2,FALSE)," " You've got the general idea, but the "Or" function requires that each argument contains the "greater than" part (see below). To make the formula more readable I have now separated the vlookup into column C & moved the other columns across (ie the formula below goes into col D, your second amount is in col F, & the table goes in col G & H). This results in: cell C1 =VLOOKUP($A1,$G$1:$H$6,2,FALSE) cell D1 =IF(ISNA($C1),"Buyer not in approved list",IF(OR($B1$C1,$F1$C1),"Amount Excess","Transactional amount within")) and for conditional formatting: =IF(ISNA($C1),"TRUE",IF(OR($B1$C1,$F1$C1),"TRUE" ,"FALSE")) and finally, "Lastly, your equation show integer 2. Does this represent the 2 columns i.e. -$D$1:$E$6-." Yes, "$D$1:$E$6" represents the 2 columns & should not have had the minus signs at each end. As shown above I have changed this to "$G$1:$H$6" to allow for your second amount column. hth Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=533361 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
2 Nesting questions | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |