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

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

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

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

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

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

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