Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Create Formula
I have a mail merge doc that could have 6 records with Balances in them.
What I have that works for the first 2 records; =IF(Bal20,Bal2,Bal1). Basicaly I want the last record that has a value 0 to print. How do I get this to look at all 6 records, or am I doing this the wrong way? -- Thanks for the help, Glenn |
#2
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Create Formula
Hi Glenn,
If your business rules are such that all balances after the first 0 balance are 0 also, you could use: {={Bal1}*({Bal2}=0)+{Bal2}*({Bal3}=0)+{Bal3}*({Bal 4}=0)+{Bal4}*({Bal5}=0)+{B al5}*({Bal6}=0)+{Bal6}} or even: {=Bal1*(Bal2=0)+Bal2*(Bal3=0)+Bal3*(Bal4=0)+Bal4*( Bal5=0)+Bal5*(Bal6=0)+Bal6 } No IF tests are required for these. Alternatively, if you could have 0 balances interspersed with non-0 balances, you could use: {IF{Bal6} 0 {Bal6} {IF{Bal5} 0 {Bal5} {IF{Bal4} 0 {Bal4} {IF{Bal3} 0 {Bal3} {IF{Bal2} 0 {Bal2} {Bal1}}}}}} or, if the non-0 balances are always +ve: {IF{Bal6} 0 {Bal6} {IF{Bal5} 0 {Bal5} {IF{Bal4} 0 {Bal4} {IF{Bal3} 0 {Bal3} {IF{Bal2} 0 {Bal2} {Bal1}}}}}} Cheers "Glenn" wrote in message ... I have a mail merge doc that could have 6 records with Balances in them. What I have that works for the first 2 records; =IF(Bal20,Bal2,Bal1). Basicaly I want the last record that has a value 0 to print. How do I get this to look at all 6 records, or am I doing this the wrong way? -- Thanks for the help, Glenn |
#3
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Create Formula
When I insert 1 of those into the formula it does not return anything. After
I click ok on the formula window, it just closes like I did not even add anything in. I must be doing something wrong??-- Thanks for the help, Glenn "macropod" wrote: Hi Glenn, If your business rules are such that all balances after the first 0 balance are 0 also, you could use: {={Bal1}*({Bal2}=0)+{Bal2}*({Bal3}=0)+{Bal3}*({Bal 4}=0)+{Bal4}*({Bal5}=0)+{B al5}*({Bal6}=0)+{Bal6}} or even: {=Bal1*(Bal2=0)+Bal2*(Bal3=0)+Bal3*(Bal4=0)+Bal4*( Bal5=0)+Bal5*(Bal6=0)+Bal6 } No IF tests are required for these. Alternatively, if you could have 0 balances interspersed with non-0 balances, you could use: {IF{Bal6} 0 {Bal6} {IF{Bal5} 0 {Bal5} {IF{Bal4} 0 {Bal4} {IF{Bal3} 0 {Bal3} {IF{Bal2} 0 {Bal2} {Bal1}}}}}} or, if the non-0 balances are always +ve: {IF{Bal6} 0 {Bal6} {IF{Bal5} 0 {Bal5} {IF{Bal4} 0 {Bal4} {IF{Bal3} 0 {Bal3} {IF{Bal2} 0 {Bal2} {Bal1}}}}}} Cheers "Glenn" wrote in message ... I have a mail merge doc that could have 6 records with Balances in them. What I have that works for the first 2 records; =IF(Bal20,Bal2,Bal1). Basicaly I want the last record that has a value 0 to print. How do I get this to look at all 6 records, or am I doing this the wrong way? -- Thanks for the help, Glenn |
#4
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Create Formula
You have to create each field (anything between braces) using Ctrl+F9.
-- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA Word MVP FAQ site: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. "Glenn" wrote in message ... When I insert 1 of those into the formula it does not return anything. After I click ok on the formula window, it just closes like I did not even add anything in. I must be doing something wrong??-- Thanks for the help, Glenn "macropod" wrote: Hi Glenn, If your business rules are such that all balances after the first 0 balance are 0 also, you could use: {={Bal1}*({Bal2}=0)+{Bal2}*({Bal3}=0)+{Bal3}*({Bal 4}=0)+{Bal4}*({Bal5}=0)+{B al5}*({Bal6}=0)+{Bal6}} or even: {=Bal1*(Bal2=0)+Bal2*(Bal3=0)+Bal3*(Bal4=0)+Bal4*( Bal5=0)+Bal5*(Bal6=0)+Bal6 } No IF tests are required for these. Alternatively, if you could have 0 balances interspersed with non-0 balances, you could use: {IF{Bal6} 0 {Bal6} {IF{Bal5} 0 {Bal5} {IF{Bal4} 0 {Bal4} {IF{Bal3} 0 {Bal3} {IF{Bal2} 0 {Bal2} {Bal1}}}}}} or, if the non-0 balances are always +ve: {IF{Bal6} 0 {Bal6} {IF{Bal5} 0 {Bal5} {IF{Bal4} 0 {Bal4} {IF{Bal3} 0 {Bal3} {IF{Bal2} 0 {Bal2} {Bal1}}}}}} Cheers "Glenn" wrote in message ... I have a mail merge doc that could have 6 records with Balances in them. What I have that works for the first 2 records; =IF(Bal20,Bal2,Bal1). Basicaly I want the last record that has a value 0 to print. How do I get this to look at all 6 records, or am I doing this the wrong way? -- Thanks for the help, Glenn |
#5
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Create Formula
Hi Glen,
For whichever formula you're using, you need to create the field braces (i.e. '{ }") in pairs via Ctrl-F9 - you can't just type them. The second example is the simplest in this respect, since it uses only one field to hold the whole equation. With the last two examples, you'll see they're nested six levels deep. To replicate them, I'd suggest working from the right-hand end, in which case you'd start off by pressing Ctrl-F9 six times in succession, to create '{ { { { { { } } } } } }', adding 'Bal1' to the central pair, then moving to the left and inserting another field for 'Bal2' (i.e. '{ { { { {{Bal2} {Bal1} } } } } }'), and so on. Note too that, for the most part, it's important to maintain the spacing (or lack thereof) I've indicated in the examples. Cheers "Glenn" wrote in message ... When I insert 1 of those into the formula it does not return anything. After I click ok on the formula window, it just closes like I did not even add anything in. I must be doing something wrong??-- Thanks for the help, Glenn "macropod" wrote: Hi Glenn, If your business rules are such that all balances after the first 0 balance are 0 also, you could use: {={Bal1}*({Bal2}=0)+{Bal2}*({Bal3}=0)+{Bal3}*({Bal 4}=0)+{Bal4}*({Bal5}=0)+{B al5}*({Bal6}=0)+{Bal6}} or even: {=Bal1*(Bal2=0)+Bal2*(Bal3=0)+Bal3*(Bal4=0)+Bal4*( Bal5=0)+Bal5*(Bal6=0)+Bal6 } No IF tests are required for these. Alternatively, if you could have 0 balances interspersed with non-0 balances, you could use: {IF{Bal6} 0 {Bal6} {IF{Bal5} 0 {Bal5} {IF{Bal4} 0 {Bal4} {IF{Bal3} 0 {Bal3} {IF{Bal2} 0 {Bal2} {Bal1}}}}}} or, if the non-0 balances are always +ve: {IF{Bal6} 0 {Bal6} {IF{Bal5} 0 {Bal5} {IF{Bal4} 0 {Bal4} {IF{Bal3} 0 {Bal3} {IF{Bal2} 0 {Bal2} {Bal1}}}}}} Cheers "Glenn" wrote in message ... I have a mail merge doc that could have 6 records with Balances in them. What I have that works for the first 2 records; =IF(Bal20,Bal2,Bal1). Basicaly I want the last record that has a value 0 to print. How do I get this to look at all 6 records, or am I doing this the wrong way? -- Thanks for the help, Glenn |
#6
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Create Formula
After using cntrl-F9 that formula works. When I do alt-F9 I can see the
formula, when I toggle off I get Error! Bookmark not defined. I found another post on this, so I'll see if I can fix it. Thanks, -- Thanks for the help, Glenn "macropod" wrote: Hi Glen, For whichever formula you're using, you need to create the field braces (i.e. '{ }") in pairs via Ctrl-F9 - you can't just type them. The second example is the simplest in this respect, since it uses only one field to hold the whole equation. With the last two examples, you'll see they're nested six levels deep. To replicate them, I'd suggest working from the right-hand end, in which case you'd start off by pressing Ctrl-F9 six times in succession, to create '{ { { { { { } } } } } }', adding 'Bal1' to the central pair, then moving to the left and inserting another field for 'Bal2' (i.e. '{ { { { {{Bal2} {Bal1} } } } } }'), and so on. Note too that, for the most part, it's important to maintain the spacing (or lack thereof) I've indicated in the examples. Cheers "Glenn" wrote in message ... When I insert 1 of those into the formula it does not return anything. After I click ok on the formula window, it just closes like I did not even add anything in. I must be doing something wrong??-- Thanks for the help, Glenn "macropod" wrote: Hi Glenn, If your business rules are such that all balances after the first 0 balance are 0 also, you could use: {={Bal1}*({Bal2}=0)+{Bal2}*({Bal3}=0)+{Bal3}*({Bal 4}=0)+{Bal4}*({Bal5}=0)+{B al5}*({Bal6}=0)+{Bal6}} or even: {=Bal1*(Bal2=0)+Bal2*(Bal3=0)+Bal3*(Bal4=0)+Bal4*( Bal5=0)+Bal5*(Bal6=0)+Bal6 } No IF tests are required for these. Alternatively, if you could have 0 balances interspersed with non-0 balances, you could use: {IF{Bal6} 0 {Bal6} {IF{Bal5} 0 {Bal5} {IF{Bal4} 0 {Bal4} {IF{Bal3} 0 {Bal3} {IF{Bal2} 0 {Bal2} {Bal1}}}}}} or, if the non-0 balances are always +ve: {IF{Bal6} 0 {Bal6} {IF{Bal5} 0 {Bal5} {IF{Bal4} 0 {Bal4} {IF{Bal3} 0 {Bal3} {IF{Bal2} 0 {Bal2} {Bal1}}}}}} Cheers "Glenn" wrote in message ... I have a mail merge doc that could have 6 records with Balances in them. What I have that works for the first 2 records; =IF(Bal20,Bal2,Bal1). Basicaly I want the last record that has a value 0 to print. How do I get this to look at all 6 records, or am I doing this the wrong way? -- Thanks for the help, Glenn |
#7
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Create Formula
Hi Glenn,
The 'Error! Bookmark not defined' message could mean you've mistyped one of the variables. In fact, given that you originally mentioned using this in "a mail merge doc", I suspect these are actually merged records. In that case, each of the variables needs to be input as a mailmerge field - simply inputting the mailmerge variable names won't do. If you set up the mailmerge with the various fields (ie «Bal1» «Bal2» «Bal3» «Bal4» «Bal5» «Bal6»), then you can copy & paste them into the formulae like: {=«Bal1»*(«Bal2»0)+«Bal2»*(«Bal3»=0)+«Bal3»*(«Bal4 »=0)+«Bal4»*(«Bal5»=0)+«Ba l5»*(«Bal6»=0)+«Bal6»} or {IF«Bal6» 0 «Bal6» {IF«Bal5» 0 «Bal5» {IF«Bal4» 0 «Bal4» {IF«Bal3» 0 «Bal3» {IF«Bal2» 0 «Bal2» «Bal1»}}}}} or {IF«Bal6» 0 «Bal6» {IF«Bal5» 0 «Bal5» {IF«Bal4» 0 «Bal4» {IF«Bal3» 0 «Bal3» {IF«Bal2» 0 «Bal2» «Bal1»}}}}} Cheers "Glenn" wrote in message ... After using cntrl-F9 that formula works. When I do alt-F9 I can see the formula, when I toggle off I get Error! Bookmark not defined. I found another post on this, so I'll see if I can fix it. Thanks, -- Thanks for the help, Glenn "macropod" wrote: Hi Glen, For whichever formula you're using, you need to create the field braces (i.e. '{ }") in pairs via Ctrl-F9 - you can't just type them. The second example is the simplest in this respect, since it uses only one field to hold the whole equation. With the last two examples, you'll see they're nested six levels deep. To replicate them, I'd suggest working from the right-hand end, in which case you'd start off by pressing Ctrl-F9 six times in succession, to create '{ { { { { { } } } } } }', adding 'Bal1' to the central pair, then moving to the left and inserting another field for 'Bal2' (i.e. '{ { { { {{Bal2} {Bal1} } } } } }'), and so on. Note too that, for the most part, it's important to maintain the spacing (or lack thereof) I've indicated in the examples. Cheers "Glenn" wrote in message ... When I insert 1 of those into the formula it does not return anything. After I click ok on the formula window, it just closes like I did not even add anything in. I must be doing something wrong??-- Thanks for the help, Glenn "macropod" wrote: Hi Glenn, If your business rules are such that all balances after the first 0 balance are 0 also, you could use: {={Bal1}*({Bal2}=0)+{Bal2}*({Bal3}=0)+{Bal3}*({Bal 4}=0)+{Bal4}*({Bal5}=0)+{B al5}*({Bal6}=0)+{Bal6}} or even: {=Bal1*(Bal2=0)+Bal2*(Bal3=0)+Bal3*(Bal4=0)+Bal4*( Bal5=0)+Bal5*(Bal6=0)+Bal6 } No IF tests are required for these. Alternatively, if you could have 0 balances interspersed with non-0 balances, you could use: {IF{Bal6} 0 {Bal6} {IF{Bal5} 0 {Bal5} {IF{Bal4} 0 {Bal4} {IF{Bal3} 0 {Bal3} {IF{Bal2} 0 {Bal2} {Bal1}}}}}} or, if the non-0 balances are always +ve: {IF{Bal6} 0 {Bal6} {IF{Bal5} 0 {Bal5} {IF{Bal4} 0 {Bal4} {IF{Bal3} 0 {Bal3} {IF{Bal2} 0 {Bal2} {Bal1}}}}}} Cheers "Glenn" wrote in message ... I have a mail merge doc that could have 6 records with Balances in them. What I have that works for the first 2 records; =IF(Bal20,Bal2,Bal1). Basicaly I want the last record that has a value 0 to print. How do I get this to look at all 6 records, or am I doing this the wrong way? -- Thanks for the help, Glenn |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create multiple documents from one and vice versa | Microsoft Word Help | |||
Looking for shortcut for placing frequently used formula in table | Microsoft Word Help | |||
Formula - page, numpages,...field | Microsoft Word Help | |||
Should be able to create a merged letter document and envelope do. | Mailmerge | |||
I need a formula of amount of hours from two times in two cell? | Tables |