Convert excel formula to Alteryx for Quartiles calculation

Question

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

Quartile Calculation Sample Data.xlsx

CharlieS · Accepted Answer

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 Answer

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