Convert excel formula to Alteryx for Quartiles calculation

[EileenTX]

I need to convert excel formula to alteryx formula. I spent some time on it and did not get a good solution. Anyone has an idea on this? Appreciate help! 

Lower Quartile =IF(MOD(COUNT(F2:O2),4)=0,AVERAGE(SMALL((F2:O2),COUNT(F2:O2)/4),SMALL((F2:O2),1+COUNT(F2:O2)/4)),SMALL((F2:O2),1+INT(COUNT(F2:O2)/4)))

Upper Quartile

=IF(MOD(COUNT(F2:O2),4)=0,AVERAGE(SMALL((F2:O2),3*COUNT(F2:O2)/4),SMALL((F2:O2),3*COUNT(F2:O2)/4+1)),SMALL((F2:O2),INT(3*COUNT(F2:O2)/4+1)))

Sample data Attached

[CharlieS · Accepted solution]

Hi @EileenTX 

As I'm sure you've figured out, there's different ways to calculate quartiles at work here. Excel (and "OECD" in this case) uses a percentile method, and "IRS" is what I consider a classic/median method. Both are possible in Alteryx and I'll show you how.

OECD/Percentile - The Summarize tool has the Numeric > Percentile option, which you can then set to 25/75 at the bottom of the tool. This returns the values you're looking for.

IRS/Median - Find the Median value with the Summarize tool using Numeric > Median. This value can be used to filter the values of each range to those above/below. Now find the median of the records above (quartile 3) or below (quartile 1). 

Check out the attached example to see these calculations in action and let me know if you have any questions. 

[BrandonB · Accepted solution]

You can pull quartiles by using the percentile functionality of the summarize tool. Please see the following link for an example: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Calculating-Quartiles/td-p/28479

[EileenTX]

It works great and it was exactly I was looking for!  I understand how to get the percentile using summarize tool but did not think I should find the median using summarized tool for quartile-IRS. Really appreciate your help!