Skip to main content

Rank PVA Deviations in Excel

Joshua earned an MBA from USF and writes mostly about software and technology.

rank-pva-deviations-in-excel

The screenshot below shows planned and actual hour deviations for several processes. Each process has a deviation, deviation percentage, and ranking. Below I walk through the calculations for the deviations followed by how to rank each process by deviation.

rank-pva-deviations-in-excel

The deviation is the simplest calculation showing the difference between the actual and planned hours.

rank-pva-deviations-in-excel

In this calculation, the planned hours will be considered a fixed base value. The planned hours are subtracted from the actual hours.

rank-pva-deviations-in-excel

Next, the deviation percentage is calculated.

rank-pva-deviations-in-excel

The percentage deviation calculation is shown below where the difference between the actual hours and planned hours is divided by planned hours (base value).

rank-pva-deviations-in-excel

In the last column, each process is ranked. Since there are 8 processes being ranked, all of the processes will be ranked 1-8. The ranking is made possible using the RANK function.

rank-pva-deviations-in-excel

To use the RANK function, start by typing the function.

=RANK(

Next, add the cell that contains the PVA deviation that needs to be ranked.

=RANK(E3,

The final argument added to the formula is the whole range of deviations.

=RANK(E3,E3:E10)

Note that dollar signs will need to be added to the row number to be able to drag the formula to other rows.

=RANK(E3,E$3:E$10)

rank-pva-deviations-in-excel

If you would like to add formatting to the ranking column, color scales can be added. Start this process by selecting the column then click on the conditional formatting under the home tab.

rank-pva-deviations-in-excel

Select the color scales button and your preferred color scaling option.

rank-pva-deviations-in-excel

The color scale option used in the example shows the rank with the highest positive deviation in red and the highest negative deviation in green. Neutral color shades are shown for number rankings close to the center.

rank-pva-deviations-in-excel

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2022 Joshua Crowder