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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Forum statistics

Threads
1,224,822
Messages
6,181,164
Members
453,021
Latest member
Justyna P

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