Hi,
i have got an wingrid in which i need to show the summary of all the rows, but one of the columns is a calculated column in which even the summary needs to be calculated ..
i would like to check for the divide by zero exception ..
is there anyway by which i can check before i assign the formula
summaryPercentageAppliedForReceived.Formula = "Sum([" + _applliedForAmountLabel + "]) / " + "Sum([" + _ReceivedAmountLabel + "])" ;
but this will work as long as the "Sum([" + _ReceivedAmountLabel + "])" is greater than zero . else it will disaply something Like NaN
so what i am looking for is something like
if ( "Sum([" + _ReceivedAmountLabel + "])" > 0)
{
"Sum([" + _applliedForAmountLabel + "]) / " + "Sum([" + _ReceivedAmountLabel + "])" ;
}
else
" 0 ";
can you please let me know if there is any other way i can do this.
thank you for your help in advance,
Hi Mike,
Thank you very much for your reply, i think i actually made some mistake while formatting so when i actually made the changes and changed the formula into
"if ( Sum ( [ " + _ReceivedAmountLabel + " ] ) > 0 , Sum ([" + _ReceivedAmountLabel + " ] ) / Sum ( [ " + _applliedForAmountLabel + " ] ) , 0 )" ;
it is working fine now.
i have also have one problem with the Formatting of the cell.
i need to have a percentage "%" symbol in one column along with the value.
if you can help me on this as well it will be great.
Thank you very much for your help.
It's really hard to read an interpret the code like this. I might be better if you did a Debug.Writeline and looked at the resulting string of your formula.
But I do see some obvious errors here.
raghav_sai said:summaryPercentageAppliedForReceived.Formula = if ( "Sum([" + _applliedForAmountLabel + "])" > 0, "Sum([" + _applliedForAmountLabel + "]) / "+ "Sum([" + _ReceivedAmountLabel + "])" , 0);
You seem to have placed the "if" outside of the string here. "if" is a function - it needs to be part of the string.
raghav_sai said:summaryPercentageAppliedForReceived.Formula = "if ( " + "Sum([ _applliedForAmountLabel ] > 0 " + "," + " Sum([ _applliedForAmountLabel]) / Sum([ _ReceivedAmountLabel ]) " + ", " + " 0)" ;
You are missing some parens here.
raghav_sai said:summaryPercentageAppliedForReceived.Formula = "if ( Sum([ _applliedForAmountLabel ]) > 0 , Sum([ _applliedForAmountLabel]) / Sum([ _ReceivedAmountLabel ]) , 0)" ;
A #REF error means one of the references here does not exist. Either applliedForAmountLabel or _ReceivedAmountLabel are not valid column names.
Thank you for your reply,
may be i am doing something wrong, because when i try to set the value for it using the following statements it actually says invalid expression
and does not complie.
summaryPercentageAppliedForReceived.Formula = if ( "Sum([" + _applliedForAmountLabel + "])" > 0, "Sum([" + _applliedForAmountLabel + "]) / "+ "Sum([" + _ReceivedAmountLabel + "])" , 0);
if i do this
summaryPercentageAppliedForReceived.Formula = "if ( " + "Sum([ _applliedForAmountLabel ] > 0 " + "," + " Sum([ _applliedForAmountLabel]) / Sum([ _ReceivedAmountLabel ]) " + ", " + " 0)" ;
it shows me syntax error.
summaryPercentageAppliedForReceived.Formula = "if ( Sum([ _applliedForAmountLabel ]) > 0 , Sum([ _applliedForAmountLabel]) / Sum([ _ReceivedAmountLabel ]) , 0)" ;
it shows me #Ref! sign in the grid column.
if possible if you can send me some sample code how i can do it. it will be helpful for me.
Thank you.
You could use the "if" function:
if ( Sum([B] > 0, Sum([A] / Sum([B], 0 )