Any Way to have a Faster Calculating formulae then this one?

[Mathman]

I have the following array formulae repeated about 2.2 million times. Any other way to have it written to increase calculation time?





=IF(E5:BB5="","",RANK(E5:BB5,$E5:$BB5,1)+COUNTIFS($E5:$BB5,$E5:$BB5,$BD$3:$DA$3,"<"&$BD$3:$DA$3))




Thank you!​

 

[Dryver14]

what are you trying to do here?

COUNTIFS($E5:$BB5,$E5:$BB5,$BD$3:$DA$3,"<"&$BD$3:$DA$3)) ?

 

[Mathman]

I have a row of 50 results, if any of those results are the same then is will look at a second condition and base the ranking on the second condition.


Hope this helps...​

 

[shg]

You could sort the data and not need to use Rank at all.

Why do you have an array as the first argument to Rank?

 

[Mathman]

I look at 50 Items, I run a loop that calculates 50K conditions then runs to the next items. When the loop is completes all the data is dumped in another tab. Once in that tab I rank each one of the 50K conditions. I really cant think how I can avoid the ranking if I want the entire process automated. Maybe I'm missing something.

The formulae was written for me by Charles Williams, It's a little over my head and cant quite figure out variations.....

 

[B___P]

Hi Mathman,
what about this?

=IFERROR(IF(E5="","",RANK.EQ(E5,$E5:$BB5,1)-1+COUNTIF($E5:E5,E5)),"")

it ranks avoiding "repetitions" and is not an array formula.

Hope this helps