Another formula question for If Then statement

[wanzek1]

This is my formula:

if Sum ({JCCD.EstHours}, {JCCD.Phase})= 0 then 0
else if Sum ({JCCD.ActualHours}, {JCCD.Phase})+{@ToDate+-Hrs}= 0
then 0
else ((Sum ({JCCD.ActualHours}, {JCCD.Phase})/(Sum ({JCCD.ActualHours}, {JCCD.Phase})+{@ToDate+-Hrs})))

This works great however they project managers would like to see something like this instead:

if Sum ({JCCD.EstHours}, {JCCD.Phase})= 0 then 0
else if Sum ({JCCD.ActualHours}, {JCCD.Phase})+{@ToDate+-Hrs}= 0
then INF [highlight #FCE94F]//Replace the 0 with and Infinity symbol or INF[/highlight]
else ((Sum ({JCCD.ActualHours}, {JCCD.Phase})/(Sum ({JCCD.ActualHours}, {JCCD.Phase})+{@ToDate+-Hrs})))

Is it possible to put text within the formula somehow? It asks me for a number if I try to change it.

Thanks!

 

[fisheromacse]

i would probably create a second formula which would evaluate the original for a 'new' result
//{@F2}
IF {@YourFormulaNameHere} = 0 THEN "INF" else {@YourFormulaNameHere};


BUT....depending on the report and other requirements, it may be easier to suppress the current formula is the value is zero and have a text box containing either the Infinity Symbol or INF which is suppressed when your current formula is not equal to zero.

 

[MCuthill]

Wanzek1,

Unfortunately, all the results of an IF statement have to be of the same data type. Ergo, either the Project managers would need to find a numeric flag of some form (not very ideal), OR you could convert all of your numeric results to Text with [blue]ToText[/blue](). It might be a little tedious to get the numbers presenting in a clean manner, but it should be possible.

If only for presentation, you may wish to leave your formula as is and create a second strictly for presentation. Other considerations/concerns would be that once converted to Text, you cannot perform math on the formula unless you convert it back (which will cause errors when/if it hits any text that cannot be converted - namely your infinity character).

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[Charliy]

You could suppress the field if it is =0, then have another formula in the same place with the Infinity symbol, that is suppressed if the other filed is not 0.

 

[wanzek1]

Thanks for all your help! I was able to do the following:

I changed the first formula to:

if Sum ({JCCD.EstHours}, {JCCD.Phase})= 0 then 0
else if Sum ({JCCD.ActualHours}, {JCCD.Phase})+{@ToDate+-Hrs}= 0
then [highlight #FCE94F]99999[/highlight] **I changed this to 99999 because otherwise I had to 0 answers
else ((Sum ({JCCD.ActualHours}, {JCCD.Phase})/(Sum ({JCCD.ActualHours}, {JCCD.Phase})+{@ToDate+-Hrs})))

I created a second formula:
if {@Performance Factor}= 99999

then 'INF'
else ToText({@Performance Factor})

Again, thanks for everyones help!