How to divide range by range and get corresponding range value.

[Kumar Shanmugam]

Hi,

I have value in column A: Trichy, Salem, Erode, Madurai, Coimbatore and Dindigul

I have value in column B: 969,1103, 1570,2822, 1015 and 1000

I have value in column C: 120,357, 534, 1195, 345 and 587

I wanted to divide range C/ range B and get new range D: 12%, 32%, 34%, 42%,34% and 59% in Cell D2




I wanted to use conditional formatting to highlight cells where arrear is highest

I used rank function formula to check whether formula is working its not working. I wanted ot know where I went wrong.

=rank(iferror(C2/B2,0),$C$2:$C$7&"/"&$B$2:$B$7,0)=1

 

[Hui]

Why not just use a helper column
D2: =C2/B2
E2: =RANK(D2,$D$2:$D$7)
Copy both down

Then apply a Conditional Format based on either Column D or E

 

[Kumar Shanmugam]

Hi
Thanks yes this can be done using helper but it will look odd if I show the helper column in dashboard report. I wanted to do this without using helper

 

[Kumar Shanmugam]

I wanted to apply conditional formatting to column c