Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.tables
|
|||
|
|||
Problem with an If Formula
Hey everyone,
I'm working on a table for a mail merge where all the information for the table is pulled from an Excel spreadsheet except for the totals and calculating the percentage of available balance. I'm trying to do an If formula to account for !Zero Divide error. My data has a lot of 0's, so it's kinda of hard to avoid. This is what I've tried and the value "if false" part of the statement won't execute. Any help is greatly appreciated. This is what I want the formula to do: If D2=0, leave the cell blank, else (D2/B2)*100 and I want the else part of the formula to be formatted as a %. { If {D2} = 0 " " {=(D2/B2)*100} \#"0%";(0%)"} |
#2
Posted to microsoft.public.word.tables
|
|||
|
|||
Problem with an If Formula
Hi Holly,
For a formula like =D2/B2, the divide by 0 test needs to apply to B2, not D2. Your existing IF test looks at D2. Subject to the above, you could code the formula like: {IF{=B2}= 0 "" {=D2/B2*100 \# "0%;(0%)"}} -- Cheers macropod [MVP - Microsoft Word] "Holly" wrote in message ... Hey everyone, I'm working on a table for a mail merge where all the information for the table is pulled from an Excel spreadsheet except for the totals and calculating the percentage of available balance. I'm trying to do an If formula to account for !Zero Divide error. My data has a lot of 0's, so it's kinda of hard to avoid. This is what I've tried and the value "if false" part of the statement won't execute. Any help is greatly appreciated. This is what I want the formula to do: If D2=0, leave the cell blank, else (D2/B2)*100 and I want the else part of the formula to be formatted as a %. { If {D2} = 0 " " {=(D2/B2)*100} \#"0%";(0%)"} |
#3
Posted to microsoft.public.word.tables
|
|||
|
|||
Problem with an If Formula
DUH!! Thanks macropod! I think I was concentrating too much on D2, lol. Now
I feel stupid. However, it still doesn't seem to work. It's returning a blank even if B2 is not 0. Hmm... Any other thoughts? I've tried about everything I can think of. Thanks for your help! "macropod" wrote: Hi Holly, For a formula like =D2/B2, the divide by 0 test needs to apply to B2, not D2. Your existing IF test looks at D2. Subject to the above, you could code the formula like: {IF{=B2}= 0 "" {=D2/B2*100 \# "0%;(0%)"}} -- Cheers macropod [MVP - Microsoft Word] "Holly" wrote in message ... Hey everyone, I'm working on a table for a mail merge where all the information for the table is pulled from an Excel spreadsheet except for the totals and calculating the percentage of available balance. I'm trying to do an If formula to account for !Zero Divide error. My data has a lot of 0's, so it's kinda of hard to avoid. This is what I've tried and the value "if false" part of the statement won't execute. Any help is greatly appreciated. This is what I want the formula to do: If D2=0, leave the cell blank, else (D2/B2)*100 and I want the else part of the formula to be formatted as a %. { If {D2} = 0 " " {=(D2/B2)*100} \#"0%";(0%)"} |
#4
Posted to microsoft.public.word.tables
|
|||
|
|||
Problem with an If Formula
Hi Holly,
Did you code the cell reference as {=B2}? The formula as suggested in my previous post certainly works for me. -- Cheers macropod [MVP - Microsoft Word] "Holly" wrote in message ... DUH!! Thanks macropod! I think I was concentrating too much on D2, lol. Now I feel stupid. However, it still doesn't seem to work. It's returning a blank even if B2 is not 0. Hmm... Any other thoughts? I've tried about everything I can think of. Thanks for your help! "macropod" wrote: Hi Holly, For a formula like =D2/B2, the divide by 0 test needs to apply to B2, not D2. Your existing IF test looks at D2. Subject to the above, you could code the formula like: {IF{=B2}= 0 "" {=D2/B2*100 \# "0%;(0%)"}} -- Cheers macropod [MVP - Microsoft Word] "Holly" wrote in message ... Hey everyone, I'm working on a table for a mail merge where all the information for the table is pulled from an Excel spreadsheet except for the totals and calculating the percentage of available balance. I'm trying to do an If formula to account for !Zero Divide error. My data has a lot of 0's, so it's kinda of hard to avoid. This is what I've tried and the value "if false" part of the statement won't execute. Any help is greatly appreciated. This is what I want the formula to do: If D2=0, leave the cell blank, else (D2/B2)*100 and I want the else part of the formula to be formatted as a %. { If {D2} = 0 " " {=(D2/B2)*100} \#"0%";(0%)"} |
#5
Posted to microsoft.public.word.tables
|
|||
|
|||
Problem with an If Formula
Yes I did. I tried hard entering the numbers into B2 and D2 and not using
the mail merge results and it still doesn't work. It still only gives me a blank. And I have been updating the field. Thanks again. Holly "macropod" wrote: Hi Holly, Did you code the cell reference as {=B2}? The formula as suggested in my previous post certainly works for me. -- Cheers macropod [MVP - Microsoft Word] "Holly" wrote in message ... DUH!! Thanks macropod! I think I was concentrating too much on D2, lol. Now I feel stupid. However, it still doesn't seem to work. It's returning a blank even if B2 is not 0. Hmm... Any other thoughts? I've tried about everything I can think of. Thanks for your help! "macropod" wrote: Hi Holly, For a formula like =D2/B2, the divide by 0 test needs to apply to B2, not D2. Your existing IF test looks at D2. Subject to the above, you could code the formula like: {IF{=B2}= 0 "" {=D2/B2*100 \# "0%;(0%)"}} -- Cheers macropod [MVP - Microsoft Word] "Holly" wrote in message ... Hey everyone, I'm working on a table for a mail merge where all the information for the table is pulled from an Excel spreadsheet except for the totals and calculating the percentage of available balance. I'm trying to do an If formula to account for !Zero Divide error. My data has a lot of 0's, so it's kinda of hard to avoid. This is what I've tried and the value "if false" part of the statement won't execute. Any help is greatly appreciated. This is what I want the formula to do: If D2=0, leave the cell blank, else (D2/B2)*100 and I want the else part of the formula to be formatted as a %. { If {D2} = 0 " " {=(D2/B2)*100} \#"0%";(0%)"} |
#6
Posted to microsoft.public.word.tables
|
|||
|
|||
Problem with an If Formula
Hi Holly,
Perhaps you'll get better results if you embed the corresponding mergefields in the formula: {QUOTE{SET Numerator {MERGEFIELD Data1}}{SET Denominator {MERGEFIELD Data2}}{IF{Denominator}=0 "" {=Numerator*100/Denominator \# "0%;(0%)"}} or {QUOTE{SET Numerator «Data1»}{SET Denominator «Data2»}{IF{Denominator}=0 "" {=Numerator*100/Denominator \# "0%;(0%)"}} where 'Data1' and 'Data2' are your mergefield names. The above might look a bit complicated, but it allows for the possibility that the mergefields might sometimes be empty, rather than having 0s. Note too that the field brace pairs (ie '{ }') for the above examples (and in my previous posts) are created via Ctrl-F9 - you can't simply type them or copy & paste them from the NG message. Likewise, you can't type the chevrons (ie '« »') - they're part of the actual mergefields, which you can insert from the mailmerge toolbar. -- Cheers macropod [MVP - Microsoft Word] "Holly" wrote in message ... Yes I did. I tried hard entering the numbers into B2 and D2 and not using the mail merge results and it still doesn't work. It still only gives me a blank. And I have been updating the field. Thanks again. Holly "macropod" wrote: Hi Holly, Did you code the cell reference as {=B2}? The formula as suggested in my previous post certainly works for me. -- Cheers macropod [MVP - Microsoft Word] "Holly" wrote in message ... DUH!! Thanks macropod! I think I was concentrating too much on D2, lol. Now I feel stupid. However, it still doesn't seem to work. It's returning a blank even if B2 is not 0. Hmm... Any other thoughts? I've tried about everything I can think of. Thanks for your help! "macropod" wrote: Hi Holly, For a formula like =D2/B2, the divide by 0 test needs to apply to B2, not D2. Your existing IF test looks at D2. Subject to the above, you could code the formula like: {IF{=B2}= 0 "" {=D2/B2*100 \# "0%;(0%)"}} -- Cheers macropod [MVP - Microsoft Word] "Holly" wrote in message ... Hey everyone, I'm working on a table for a mail merge where all the information for the table is pulled from an Excel spreadsheet except for the totals and calculating the percentage of available balance. I'm trying to do an If formula to account for !Zero Divide error. My data has a lot of 0's, so it's kinda of hard to avoid. This is what I've tried and the value "if false" part of the statement won't execute. Any help is greatly appreciated. This is what I want the formula to do: If D2=0, leave the cell blank, else (D2/B2)*100 and I want the else part of the formula to be formatted as a %. { If {D2} = 0 " " {=(D2/B2)*100} \#"0%";(0%)"} |
#7
Posted to microsoft.public.word.tables
|
|||
|
|||
Problem with an If Formula
Macropod,
It works!! I didn't know about the whole ctrl+F9. How foolish of me. Anyway, thanks a lot for all your help! I appreciate it! Thank you! Holly "macropod" wrote: Hi Holly, Perhaps you'll get better results if you embed the corresponding mergefields in the formula: {QUOTE{SET Numerator {MERGEFIELD Data1}}{SET Denominator {MERGEFIELD Data2}}{IF{Denominator}=0 "" {=Numerator*100/Denominator \# "0%;(0%)"}} or {QUOTE{SET Numerator «Data1»}{SET Denominator «Data2»}{IF{Denominator}=0 "" {=Numerator*100/Denominator \# "0%;(0%)"}} where 'Data1' and 'Data2' are your mergefield names. The above might look a bit complicated, but it allows for the possibility that the mergefields might sometimes be empty, rather than having 0s. Note too that the field brace pairs (ie '{ }') for the above examples (and in my previous posts) are created via Ctrl-F9 - you can't simply type them or copy & paste them from the NG message. Likewise, you can't type the chevrons (ie '« »') - they're part of the actual mergefields, which you can insert from the mailmerge toolbar. -- Cheers macropod [MVP - Microsoft Word] "Holly" wrote in message ... Yes I did. I tried hard entering the numbers into B2 and D2 and not using the mail merge results and it still doesn't work. It still only gives me a blank. And I have been updating the field. Thanks again. Holly "macropod" wrote: Hi Holly, Did you code the cell reference as {=B2}? The formula as suggested in my previous post certainly works for me. -- Cheers macropod [MVP - Microsoft Word] "Holly" wrote in message ... DUH!! Thanks macropod! I think I was concentrating too much on D2, lol. Now I feel stupid. However, it still doesn't seem to work. It's returning a blank even if B2 is not 0. Hmm... Any other thoughts? I've tried about everything I can think of. Thanks for your help! "macropod" wrote: Hi Holly, For a formula like =D2/B2, the divide by 0 test needs to apply to B2, not D2. Your existing IF test looks at D2. Subject to the above, you could code the formula like: {IF{=B2}= 0 "" {=D2/B2*100 \# "0%;(0%)"}} -- Cheers macropod [MVP - Microsoft Word] "Holly" wrote in message ... Hey everyone, I'm working on a table for a mail merge where all the information for the table is pulled from an Excel spreadsheet except for the totals and calculating the percentage of available balance. I'm trying to do an If formula to account for !Zero Divide error. My data has a lot of 0's, so it's kinda of hard to avoid. This is what I've tried and the value "if false" part of the statement won't execute. Any help is greatly appreciated. This is what I want the formula to do: If D2=0, leave the cell blank, else (D2/B2)*100 and I want the else part of the formula to be formatted as a %. { If {D2} = 0 " " {=(D2/B2)*100} \#"0%";(0%)"} |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MS Word table - problem with conditional formula | Tables | |||
Problem with Formula | Tables | |||
Word table formula problem. | New Users | |||
how to align caption of formula right whereas formula itself left | Microsoft Word Help | |||
Need a formula | Microsoft Word Help |