Formula help required

[Dee]

Hi Guys,


I have two columns say Rank & Confidence.

if ranking is more than or equal to 9 then the minimum of corresponding confidence level should come in Cell A3.


i tried using extra column but was not satisfied, i guess there should be some smarter way of doing it using formula.


Thanking you in advance,

Dee

 

[Faseeh]

Hi Dee,


When you have only two columns then you will have only one value of Confidence against respective value of rank so how you are going to find the min in case of a single value? Please explain.


Regards,

Faseeh

 

[Dee]

It's not one value, range of Rank column is B1 to B10 & Range of Confidence column is C1 to C10.Answer required in A3.

Hope i am clear

 

[Faseeh]

Hi Dee,


I assume your data is present in this manner:

[pre]

Confidence
Ranking	    Min	     Max
0	    0	     5
5	    10	     15
10	    20	     25
15	    30	     35
20	    40 	     45
25	    50	     55[/pre]
...enter / use ..


=VLOOKUP($B$3,C3:D12,2)

...to look-up to the Min value of confidence only. Cell B3 contains the Rank Value. If this is not appropriate please upload a sample file.


Regards,

Faseeh

 

[Dee]

Hi Faseeh,

My data looks like this


Rank Confidence level (%)

1 80

3 45

9 56

27 80

81 45

27 75


First it considers only >= 9 ranking and from that corresponding confidence levels it has to pick the minimum conf level

In this example, >= 9 values are 56%,80%,45 & 75%. So the answer will be =Min(56,80,45,75) ie., 45%


I need above solution in formula. How can i write a formula for this?

 

[srinidhi]

=IF(A2>=9,MIN($b$2:$b$8),0), change the cell values as required.Hope this what you want

 

[Dee]

Hi Srinidhi,


Thanks for this formula.

This will not work as Rank is range ie., instead of A2 i have to take a range say A2 to A8

 

[NARAYANK991]

Hi ,


You can try the following formula :


=SMALL(((Rank_Range>=9)*(Confidence_Range)),COUNTIF(Rank_Range,"<9")+1)


entered as an array formula ( using CTRL SHIFT ENTER ).


Narayan

 

[Dee]

hi Narayan,

Thank a ton...

this formula worked perfectly


Dee