Replicating an Excel Formula in Access

[hymanator]

Hello,

I have a formula in Excel which gets me the information I need, but takes over 8 hours to calculate. I am hoping that Access will be able to accelerate the calculation process.

Overview: The formula I am using calculates a peer group for a particular peer based on a profitability metric, and then determines if there are enough peer respondents in a product category to report data; if there are, it then calculates a percentile of revenue based on the peer respondents. I want to be able to manipulate the peer group parameters to affect the outcome and see how the resulting percentiles correlate to another metric we are using.

There are numerous challenges, particularly since Access lacks a percentile formula. I also am not well versed in using access and so am not confident nesting Iif statements (if that's even possible?). Thanks in advance for your help! The excel formula is copied below.

H is the profitability metric used to determine peer groups
N is the revenue
I is the product category

=IFERROR(IF($H6
1)*($H:$H
peergroup_highthreshold, PERCENTILE(IF(($N:$N>1)*($H:$H>peergroup_highthreshold)*($I:$I=$I6),$N:$N, "N/A"), P$4), IF($H6>peergrouptop_cutoff, PERCENTILE(IF(($N:$N>1)*($H:$H>peergrouptop_entry)*($I:$I=$I6),$N:$N, "N/A"), P$4), PERCENTILE(IF(($N:$N>1)*($H:$H<=($H6*peergroup_maximum))*($H:$H>=($H6*peergroup_minimum))*($I:$I=$I6),$N:$N, "N/A"),P$4)))), "Error")

Confirmed with Ctrl+Shift+Enter</peergroup_lowthreshold)*($i:$i=$i6),$n:$n,></peergroup_lowthreshold,>

 

[mole999]

Microsoft Access 2010 - Calculating Percentages - Toolbox for IT Groups, third down for how to do a percent

is that the only formula you run and it takes 8 hours, what size is the data set, do you use volatile functions on your data, do you turn of calculation whilst running the query

 

[hymanator]

The 8 hour time is in excel, but yes that is the only formula I have running and it takes 8 hours to calculate. The data set is 160k rows. And I have not been able to successfully run a query as I cannot write it to meet my needs at this time.

I am not trying to create a percentage, but rather the percentile (pulling out the reference data value corresponding to a specific point, as opposed to the reference datapoint divided by the entire reference dataset). Thank you!

 

[mole999]

have you tried modifying the column ranges from $N:$N to $N1:$N170000 and see how that runs, I'm not sure if have a million rows to work against is slower, and i don't know percentile, hence prompting for percent

 

[hymanator]

No, I will try that and see if it helps. Thank you!