Formula to rank with condition

[Jet Fusion]

Hi all

Can someone please help with a formula, I've managed to use a formula to rank the entries from lowest to highest but i need it to also rank on a condition.

The condition is only values are ranked when the two columns have a yes. See attached.

Also i need for the lowest value and company name then to be copied to the above cells see attached.

Thank you in advance.

 

[Nebu]

Hi:

May be this? Formulas in yellow cells.

Thanks

 

[Jet Fusion]

Hi Nebu

Thanks for reply.

Only problem (should of mentioned it) is that it must ignore 0 values.

 

[Nebu]

Hi:

Have modified the formulas.

Thanks

 

[Jet Fusion]

Hi

Thanks, not sure if i'm doing something wrong but it seems all works well untill i input the formula for the lowest value with the company name.

See attached original file.

Thanks

 

[Nebu]

Hi:

Its an array formula, confirm it by pressing Control+Shift+Enter keys

 

[Jet Fusion]

ah ok

It seems to still be showing the lowest value even if it has a N

 

[Nebu]

Hi:

Have included the condition for "N" as well...

 

[Jet Fusion]

Sorry for the bother but if you check the file that you modified it's not updating, If you change all the columns with Y & N it doesnt update the value
with the lowest

 

[Jet Fusion]

I meant change them all to Y it does not update

 

[Nebu]

Hi:

Not sure what you meant. Its working fine as per the logic you had given me or am I missing something?

Thanks

 

[Jet Fusion]

Hi,

Sorry I might not of been clear.

Let me load the file, might be easier.

 

[Nebu]

Hi:

I am not sure, when I opened the file it is showing price for "E" in F2 and Company Name "E" in F1 which is correct as per the logic given by you (both contractual & technical column "Y") and the ranking is also correct as it is the 5th lowest price.

Thanks

 

[GraH - Guido]

Hi Jet Fusion, could it be you have the calculation set to manual? Switch to Automatic in case it is.

 

[Jet Fusion]

Hi Nebu & GraH - Guido

Thanks for feedback, not sure what is going on my side, I have checked and the setting is on automatic calculation.

When i change more than one row to be Y & Y under the contractual and technical it does not seem to highlight the correct row in the ranking column to match the top in F1 & 2.

Thank you for the time and assistance.

Jet