The table below is an analysis of tasks and the variances between actuals and standard. My original intent was to rank equally column G variances. As they are both negative and positive I cannot just use the RANK function in Excel. I found a formula using SUMPRODUCT, which worked, but I do not completely understand how it works. A coworker modified the original formula in column I and the result is in column J. How is it that both fornulas work? Can someone breakdown what the SUMPRODUCT formula in column I is actually doing? I don't want to use a formula that I do not understand how it works.
Many thanks for any help.
Formula in column I:
=SUMPRODUCT((ABS(G3)<=ABS($G$3:$G$17))*($G$3:$G$17<>""))-SUMPRODUCT((ABS(G3)=ABS($G$3:$G$17))*($G$3:$G$17<>""))+1
Formula in column J:
=SUMPRODUCT((ABS(G3)<=ABS($G$3:$G$17))*($G$3:$G$17<>""))
<tbody>
</tbody>
Many thanks for any help.
Formula in column I:
=SUMPRODUCT((ABS(G3)<=ABS($G$3:$G$17))*($G$3:$G$17<>""))-SUMPRODUCT((ABS(G3)=ABS($G$3:$G$17))*($G$3:$G$17<>""))+1
Formula in column J:
=SUMPRODUCT((ABS(G3)<=ABS($G$3:$G$17))*($G$3:$G$17<>""))
<colgroup><col style="mso-width-source:userset;mso-width-alt:768;width:16pt" width="21"> <col style="mso-width-source:userset;mso-width-alt:4937;width:101pt" width="135"> <col style="mso-width-source:userset;mso-width-alt:2048; width:42pt" width="56" span="3"> <col style="mso-width-source:userset;mso-width-alt:2267;width:47pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:3474;width:71pt" width="95"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2560; width:53pt" width="70" span="2"> </colgroup><tbody> </tbody> | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
<tbody>
</tbody>
Last edited: