Skip to Content
Former Member
May 25, 2016 at 07:56 AM

If value = Null then apply next Max value


Hi All,

I wonder if anyone can help with a formula I have been working on.

I am reporting on the duration between a case being logged and resolved. If this data is recorded outwith our work hours then the value is null and we have set null to = 'NA' in the report.

I am reporting the Min, Max, Median and Average of these durations. However, if any of the values = NA then this is the Max value that is returned. How do I get crystal to bring back the real Max value.

I tried using an If Then Else statement that returns the Nth largest - 2, 3, 4, 5, 6 (hoping there wouldn't be more than 6 NA values at any given time). However, this doesn't work - value NA is still returned.

I wonder if there is an easier way to handle this issue?