I'm trying to develop a tool that will automatically generate a daily production schedule. The last feature I'm trying to add is a function to prioritize the production plan by Mfg Line Number.

Variables:
I4 = Cases On-Hand
N4 = Avg CS Sold/Wk
Q4 = Max CS Sold/Wk
AG4 = On Order (Demand)
AK4 = Cases or Amt We should Have On-Hand
AL4 = Cases or Amt We should make
BA4 = Mfg. Line Number

I'm currently taking I4 (On-Hand) subtracting AG4 (On-Order) and then dividing the result by the Q4 (Max CS Sold/Wk) to generate a %. Based on that percent I have the following formula to rank the results by Line.

=IF(BR205=0,0,(1+SUMPRODUCT(($BA$4:$BA$313=BA205)*($BR$4:$BR$313>BR205))))

I know it's a lot to ask but I could use a fresh set of eyes here. Is there a better way? How would you do it?

Any help would be greatly appreciated.

Thanks,
Jason