Maximum and Minimum from Text field

[Scheruku]

Hello All,

I'm trying to finding the count of Maximum and Minimum number of times a particular values("Estimated") has occurred continuously before new values. 

for the first row estimated has occurred two times in feb & march so the minimum count was "2", and after a break it occurred 4 time, so the maximum output is 4.

Please see the below example : 

Partner	January	Feb	March	April	May	June	July	August	September	Minimum	Max
170000001	Actual	Estimated	Estimated	Actual	Actual	Estimated	Estimated	Estimated	Estimated	2	4
270000029	Estimated	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	1	1
270000043	Estimated	Estimated	Actual	Actual	Actual	Actual	Actual	Actual	Actual	2	2
470000056	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	0	0

Please help.

Best Regards,

Scheruku.

[Kenda · Accepted solution]

Hey @Scheruku! The key to getting your output was to Transpose the data then use a couple Multi-Row Formula tools. See the attached v11.0 workflow. Hope this helps!

[Scheruku]

Hi Barnes,

Thanks much for your time... The solution is working amazingly well except when there are maximum number of estimates ...

If the number of estimates are 9, the maximum count is 9 but the minimum count is going to 2. I think this solution should be fine.

Thank you very much for your time.

[Kenda]

@Scheruku I'm glad that is working for you! In the last Formula tool, just before the Browse, you could modify the Min column's expression so that it reads:

iif(isnull([Min]),0,iif([Max]=9,9,[Min]))

This should take care of the case in which all 9 months are estimates.