Request for Excel formula for two conditions (Large to small, then small to large)

Hi Experts,


I need some help on the Excel formula.

1 I need a formula for Column D (yellow)
2 The first condition is Risk Exposure (Column B) should rank from largest to smallest.
3 The second condition is Management Preparedness (Column C) should rank from smallest to largest
4 The result should be as follows:

Ranking Risk Risk Exposure Management Prepardness Ranking
1 C1 3.2 1.6 C1
2 C4 3.2 1.6 C4
3 C5 2.5 3 C5
4 C2 2.3 5 C2
5 C3 1.4 3 C3

Assuming that the raw data starts in the format shown in the screen capture included in your sample, a formula will not sort the original data for you.

A formula can be used to show the data sorted as required in a different column, but the order of the original data will be unchanged.

Also could you elaborate more on your comment in the sample:

I need a formula instead of using sort button because I have many entity to manipulate the data.

Thanks Sixthsense (Forum Guru) for the help. However, I think didn't clearly saying my problem here. I have many entities to rank the risks. Some of the entities may not have the certain risk, so the cell would reflect as N/A. Basically whatever Forum Guru (Sixth sense) explained is correct. Just i didnt mention my risk conditions. Hope you all can help me ...Really appreciate....Help from Singapore.

Thanks all for the prompt response...I really appreciate your help. Hope you all can help me to solve this problem...

As you got two kind of sorting (Risk Exp and management preparedness) and I assume you wish the C ranking sett accordingly for every group so I've added
a macro solution as well.

Alf

Thanks ALF for the solutions. However, I am just an Excel beginner so i would appreciate using the formula.. I am not good at Macro...But your Marco results are correct. Hope you all can help me with the formula.....Thanks alot......Help from Singapore

Originally Posted by nicholascky

I am just an Excel beginner so i would appreciate using the formula.. I am not good at Macro

A 'macro' in this case is probably this simplest option, you don't need to be good with them to use them, as long as you are capable of clicking a mouse button then you are good to go.

ARRAY formula in E5, then drag down

Please Login or Register  to view this content.

To Sixthsense, jason.b75, Alf, kvsrinivasamurthy and Sanram,

Appreciate your help!!!! I have solved the problem.

Hi Expert,

There is still some error in the attached worksheet. The ranking of risk should first rank the risk exposure from the largest to lowest, then management preparedness should rank from lowest to largest.

Actually, would it be possible to rank the risk by risk identification instead of number, like C4, C6, C7?

I haven't looked at the most recent example, this is based on Sheet2 of the sample file that you provided in post #5.

Helper formula, to be entered into F5

=COUNTIF($C$5:$C$57,">"&IFERROR(C5,0))+IF(ISNA(D5),0,COUNTIFS($C$5:$C$57,C5,$D$5:$D$57,">"&D5))+COUNTIFS(C$4:C4,C5,D$4:D4,D5)+1

Formula to return risk ID to be entered into E5

=INDEX($B$5:$B$57,MATCH(ROWS(E$5:E5),$F$5:$F$57,0))

On the assumption that it might be your next question, the formula cannot copy the colour formatting of the risk ID's from column B.

If it is done with conditional formatting, then you can apply the same rules to the formula cells.

Cell formats changed manually can only be copied by macro, there is no formula that is capable of doing it.

As of your post I have prepared your file with 2 helper column which is resulting exactly as you want. Hope that it may help.


Logically ranking the risk with risk identification instead of number will never be possible. Because you will not be able to know the ranking from risk identification anyhow. So, I am not clear about that requirements.

But if you want to place a letter before the ranking then you can do this with custom format as I have done on the sample.

Hi Excel Experts,

There are still have some errors appear in the ranking. (#N/A)

My requirements are as follows:

Ranking of risk should be sort by Risk Exposure (Largest to Smallest), then by Management Preparedness (Smallest to Largest).

The ideal situation would be in the attached Sheet 2 using the sort function.

Appreciate your advice.!!

Originally Posted by nicholascky

There are still have some errors appear in the ranking. (#N/A)

I can't see anything in your thread that tells us how you wanted #N/A to be ranked, so we had to guess.

Looking at your latest sample I assume that you want #N/A at the top, in which case, please follow post #17 (the new formula is based on the same sample file, not the most recent one), replacing the first formula with this one.

=COUNTIFS($C$5:$C$57,">"&IFERROR(C5,1E+100))+IF(ISNA(D5),0,COUNTIF($C$5:$C$57,"#N/A")+COUNTIFS($C$5:$C$57,C5,$D$5:$D$57,">"&D5))+COUNTIFS(C$4:C4,C5,D$4:D4,D5)+1

Hi All Subject Matter Experts,

Thanks for your support and solution. It really solved my problem...Do i need close the thread?

Will buy you all dinner when any of you come to Singapore. Hahaha

May I know which solution is OK.

The solution given by kvsrinivasamurthy. Thanks alot...Thanks other contributors as well...The members here are very helpful and supportive.

I still fail to see how that matches what you asked for, the order of the results looks nothing like the example you provided.

This looks closer to what you asked for, unless your example was wrong.

Maybe I didn't articulate my requirements clearly in the beginning and also I am just a beginner. Some forum experts here might provided the correct solution but I didn't how to apply it.

But kvsrinivasamurthy (Excel expert) was giving me the most direct formula that i wanted. Anyway, thanks for you all.

Originally Posted by nicholascky

Some forum experts here might provided the correct solution but I didn't how to apply it.

Then maybe it is time to learn.

If you discard the suggestions that people provide simply because you don't understand how to use them, then those people will be less likely to help you next time you need it.

Thanks for the feed back.