Alteryx Designer: Calculating Quartiles in a Formula Tool Instead of in Summarize Tool

[sducey95]

I need to calculate the 25th and 75th percentiles according to a certain formula used in Excel.  Note, this is different than the normal calculation that is being utilized by the Summarize Tool.  The Excel formula is as follows:

IF(C15=0,#N/A,IF(MOD(C15,4)=0,AVERAGE(LARGE(C4:C13,CEILING(C15/4,1)),LARGE(C4:C13,CEILING(C15/4,1)+1)),LARGE(C4:C13,CEILING(C15/4,1))))

I know the LARGE and CEILING functions do not exist in Alteryx but I was curious if anyone had any ideas on how to put this Excel formula into Alteryx.  I have been seeing some threads on using the Tile tool to calculate quartiles but I'm not quite sure how that works.

Thanks!

[jdunkerley79 · Accepted solution]

You can reproduce this.

I am assuming that C15 was the count of values.

The top path sorts the values into descending order and adds a record id. This will allow the reproduction of the 'Large' function by joining onto the record ID.

Next the value of CEILING(C15/4,1) is reproduces by getting a count of records (assumed to be C15's value) and then reproducing as CEIL([Count]/4)

Next if MOD([Count],4)=0 we need to have both this index and the next one

Finally we join the recordid to the computed index and average the result.

This should produce the same values as the Excel formula

[sducey95]

Thank you!!! I added a "+ 1" to the first Index Tool and then a "- 1" to the second Index Tool and I was able to replicate this for the lower quartile.  

[EileenTX]

I need to convert excel formula to alteryx formula, Could you please help on this. Appreciate help! 

Lower Quartile IRS =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)))

Sample data attached