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,
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,
