Help requested! How to re-rank after saving value of one row (result)

uarshad83

New Member
Joined
Jul 28, 2017
Messages
1
Hi,

I am not an expert of Excel but learning as much as I can. Currently, I created a Ranking Matrix with linking formulas on 3 sheets:
1. Criteria - have some factors
2. Input - input fields
3. Ranking - calculation & final result

On ranking sheet I have results from COL - D to O with below formula:

=IF(AND(Input!$B3="L",Input!$C3="L",Input!D3="Y"),"1",IF(AND(Input!$B3="L",Input!$C3="M",Input!D3="Y"),"2",IF(AND(Input!$B3="L",Input!$C3="H",Input!D3="Y"),"3",IF(AND(Input!$B3="M",Input!$C3="L",Input!D3="Y"),"2",IF(AND(Input!$B3="M",Input!$C3="M",Input!D3="Y"),"4",IF(AND(Input!$B3="M",Input!$C3="H",Input!D3="Y"),"5",IF(AND(Input!$B3="H",Input!$C3="L",Input!D3="Y"),"3",IF(AND(Input!$B3="H",Input!$C3="M",Input!D3="Y"),"5",IF(AND(Input!$B3="H",Input!$C3="H",Input!D3="Y"),"6","0")))))))))

COL - P = Total Score (simply adding result from COL - D to O) in P
COL - Q = Total Weighted Score (multiplying values in COL D to Q with their respective factors % which is mentioned in ROW - 3) below is the formula

=IF(P4="","",D4*$D$3+E4*$E$3+F4*$F$3+G4*$G$3+H4*$H$3+I4*$I$3+J4*$J$3+K4*$K$3+L4*$L$3+M4*$M$3+N4*$N$3+O4*$O$3)

COL - R = Ranking = Based on COL - Q results I am ranking all values with formula:

=IF(Q4="","",RANK(Q4,S$4:S$100,0))+COUNTIF(Q$4:Q4,Q4)-1

It is giving me my required result in the form of 1,3,5,6,2,4... not in sequence

What I need to do next - I want to know and guidance:

1. If I can sort values automated by ranking - mean in Ranking column it will rank 1,2,3,4...in sequence and also shuffle all ranking if any ranking changed based on input sheet. Please let me know if I can do it by any formula.

2. In input sheet there are some issues on which I am working (and categorized with In Progress or Fixed) in input sheet (COL-P). So if issue is currently In Progress on Ranking sheet I am getting result as required but once issue is Fixed I want to:
- Save the Ranking of that Issue on same Row with current result (in COL D-R) and Re-Prioritize all other Issues excluding the Fixed one. Can I do it with putting some logical formulas in Excel or not.

I want to share my excel sheet here but unable to find attach button so not sure either I can attach excel sheet or not. So I tried to mentioned my maximum input to resolve my problem.

Any help and guidance will be really appreciated. I can share my file on email if anyone can help me.

Thanks for your time for reading it and response.

Profound Regards,
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top