

LinkBack  Thread Tools  Display Modes 
#1




using if and compare with multiple criteria and ranges of values
Help! I am working on a document that needs to fill in text that is dependent on amount ranges in a single mergefield. I have three ranges of values and text associated with each. The ranges are 0  417000 417001  650000 650001 and higher I can get two of the three to work but not all three at once. Here's what I have: { If { = and ( { compare { mergefield_program } = "1/1" }, { compare { mergefield_dollaramount } < 417001 } ) } = 1 "text1" "" } { If { = and ( { compare { mergefield_program } = "1/1" }, { compare { mergefield_dollaramount } > 417000 < 650001 } ) } = 1 "text2" "" } { If { = and ( { compare { mergefield_program } = "1/1" }, { compare { mergefield_dollaramount } > 650001 } ) } = 1 "text3" "" } I have tried multiple iterations of the < and > but nothing seems to work. Any suggestions? My head is starting to hurt from pounding it against this brick wall of a problem. 
#2




It's a whole lot simpler than that:
{IF{MERGEFIELD dollaramount \# 0}> 650000 "text3" {IF{MERGEFIELD dollaramount \# 0}> 417000 "text2" "text1"}} I can't see where {MERGEFIELD program} has a role.
__________________
Cheers, Paul Edstein [MS MVP  Word] 
#3




Quote:
The program field comes into play because I have several different programs that can also impact the text. 1/1 3/1 5/1 and 7/1. The program in conjunction with the amount determines the text that is used. the group of three alternatives i posted are repeated for each of the programs. 
#4




In that case, you could use:
{IF{MERGEFIELD program}= "1/1" {IF{MERGEFIELD dollaramount \# 0}> 650000 "text3" {IF{MERGEFIELD dollaramount \# 0}> 417000 "text2" "text1"}}} {IF{MERGEFIELD program}= "3/1" {IF{MERGEFIELD dollaramount \# 0}> 650000 "text3" {IF{MERGEFIELD dollaramount \# 0}> 417000 "text2" "text1"}}} {IF{MERGEFIELD program}= "5/1" {IF{MERGEFIELD dollaramount \# 0}> 650000 "text3" {IF{MERGEFIELD dollaramount \# 0}> 417000 "text2" "text1"}}} {IF{MERGEFIELD program}= "7/1" {IF{MERGEFIELD dollaramount \# 0}> 650000 "text3" {IF{MERGEFIELD dollaramount \# 0}> 417000 "text2" "text1"}}}
__________________
Cheers, Paul Edstein [MS MVP  Word] 
#5




Quote:
I've been bouncing off this problem for two days. Most of what I know about if then in Word is self taught from what I can find on the net so this is extremely helpful. Thanks again. 
#6




You might also want to check out the Sticky 'Mailmerge Tips & Tricks' thread at the top of this forum: http://www.msofficeforums.com/mailm...pstricks.html
__________________
Cheers, Paul Edstein [MS MVP  Word] 
#7




Running into an unexpected issue with this.
The solution works perfectly, except.... For each of the types I have, 1/2 3/1 and so on, they can be either plain 2/1 or 1/1 special. I need to be able to insert one group of text for the various amount ranges if it's a plain 1/1 and a different group of text baded on thevalue range if it's a 1/1 special. I've tried using various wildcards but nothing seems to work. 
#8




Figured it out!
I added a third condition to the solution already provided. {if {mergefield_program} = "1/1 ARM*" {if {mergefield_program}= "*  special"{if mergefield_amount}>650000 I then used the exact same syntax except the 2nd statement is "<>  special". That seems to do the trick. 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
I need to add multiple values based on multiple criteria in a cell not sure what to do  AUHAMM  Excel  3  10272014 09:11 PM 
Lookup multiple values and compare different scenarios to get a specific result  mws  Excel  5  05242014 04:52 AM 
transpose values based on mulitple criteria  mlttkw  Excel  1  12062013 02:00 AM 
Compare content of two ranges INCLUDING FORMATTING  delasson  Word VBA  3  12102012 03:54 AM 
Change values in cells based on criteria  SaneMan  Excel Programming  2  02022012 07:58 AM 