en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Jason Pye  
#1 Posted : Friday, April 19, 2013 11:28:42 AM(UTC)
Jason Pye
Rank: Newbie

Groups: extranet\Forum
Joined: 4/19/2013(UTC)
Posts: 3

Hi there,

when i input the following formula i get 'Math Error' for some rows where there isn't a value.

I Beleive i need to use an IF THEN ELSE formula.

Could somebody help me structure the formula to allow me to hide the 'Math Error' message.

Cash Margin / Net Sales = Cash Margin %

Formula: sum(0,0,m6) / sum(0,0,m2) It is formatted as a %.

thanks for your help

Jason
David L. Bojsen  
#2 Posted : Friday, April 19, 2013 11:52:12 AM(UTC)
David L. Bojsen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 3/21/2013(UTC)
Posts: 19

jpye@ryman.co.uk wrote:
Hi there,

when i input the following formula i get 'Math Error' for some rows where there isn't a value.

I Beleive i need to use an IF THEN ELSE formula.

Could somebody help me structure the formula to allow me to hide the 'Math Error' message.

Cash Margin / Net Sales = Cash Margin %

Formula: sum(0,0,m6) / sum(0,0,m2) It is formatted as a %.

thanks for your help

Jason


Hi Jason

Please try the following formula:
if sum(0,0,m2) = 0 then sum(0,0,m6) else sum(0,0,m6) / sum(0,0,m2)

Hope this helps.
Venlig hilsen / Best regards

David L. Bojsen
Kapacity A/S

UserPostedImage

Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of my employer Kapacity A/S.
Jason Pye  
#3 Posted : Friday, April 19, 2013 2:07:27 PM(UTC)
Jason Pye
Rank: Newbie

Groups: extranet\Forum
Joined: 4/19/2013(UTC)
Posts: 3

Thanks

that works perfectly

is there a way to blank out the field rather than returning 0.0%

If i was in excel i would do the following...

=IF(A1=0,"",A1)

hope that makes sense

thanks again

Jason
Ole  
#4 Posted : Friday, April 19, 2013 3:59:40 PM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 324

You can use a visibility agent to hide values of 0 (or values of 99999, if that is what you chose in your if-then-else statement):

Hide value when value = 0
I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

UserPostedImage Ole Dyring
Steve Blincoe  
#5 Posted : Friday, September 19, 2014 2:30:49 PM(UTC)
Steve Blincoe
Rank: Newbie

Groups: extranet\Forum
Joined: 9/15/2014(UTC)
Posts: 1

- thanks for the above info; very useful - have managed to adapt this to my own reporting.

another question: is it possible to return a text string in response to a THEN

ie i was thinking where the value came back "Math Error" i could return "Not as Plan".
if i got that to work, i might also look to anything above 100% returning "Complete"
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error