select maximum value based on condition 3

[AzizKamal]

In an excel file, I want to highlight a row conditionally. My columns are as follows:
Column A: Serial Number
Column B: Productivity Pages
Column C: Cancellation Percentage
Column D: Minimum Cancellation Percentage
Column E: Maximum Productivity Pages

My goal is to yellow-highlight that row that meets the following criteria:
First check for those rows that have minimum cancellation percentage (Column C).
Within the rows with minimum cancellation percentage, find that row that has maximum productivity pages (Column B).

The sample values are as follows:

Serial_Number Productivity_Pages Cancellation Percentage
1 900 3.00
2 1500 1.00
3 200 9.00
4 1400 0.00
5 1300 0.00

In this sample, the formula needs to first select serial number 4 and 5, because they have minimum cancellation percentages. Then within 4 and 5, the formula needs to select serial number 4, because it has maximum productivity pages.

To build the formula for applying in conditional formatting, I entered the following formula in Column D cell D2:

=IF(C2=MIN($C$2:$C$6),1,0)

and copied the formula up to C6. This showed 1 for serial number 4 and 5.

Now the harder part is to extract maximum from serial number 4 and 5. In column E, I entered the following formula:

=MAX((D2:D6=1)*B2:B6)

and pressed ctrl+shift+enter to make it an array formula. The result showed 1400, which is correct. However, when I copy the array formula up to E6, due to the range D26 and B2:B6 being flexible, the formula does not work correctly. If I lock the range using $ sign, the formula does not work in that case too.

 

[vgulielmus]

May be this array formula ?

=MAX($B$2:$B$6*$D$2:$D$6)

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Andrzejek]

How about this:
1. Data - Filter
2. Col C - Select 0 (minimum)
3. Col B - Select 1400 (Max)
4. Highlight remining row
5. Click Filter again

You are done.

[pre]
Serial_Number Productivity_Pages Cancellation Percentage
1 900 3.00
2 1500 1.00
3 200 9.00
[highlight #FCE94F]4 1400 0.00[/highlight]
5 1300 0.00
[/pre]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[vgulielmus]

Another proposition

[pre]A B C D E F G H I J K
Serialr Producs Cancell
1 900 3 0 1400 0
2 1500 1 0 0
3 200 9 0 0
4 1400 0 1400 5
5 1300 0 1300 0

formula for column I
=IF(C2=MIN($C$2:$C$6);INDIRECT("B"&ROW(C2));0)
formula for J2
=MAX($I$2:$I$6)

formula for column K
=IF(I2=$J$2;ROW(I2);0)[/pre]


Select A2:C6 and activate A2 (use TAb if needed)
Home-> Conditional Formatting->New Rule->Use a formula to determine...
Fill "Format values where this formula is true:" with =ROW(A2)=K2
Select the format you need and OK

Select A2:C6 and activate B2 (use TAb if needed)
apply the same conditional formatting

Select A2:C6 and activate C2 (use TAb if needed)
apply the same conditional formatting


Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[AzizKamal]

Thank you so much.

Solution A: =MAX($B$2:$B$6*$D$2:$D$6) did not give the desired result.
Solution B: Using Data -> Filter works perfectly. It is very precise and works accurately. It works just as if I am implementing my words in Excel commands.
Solution C: Using =ROW(A2)=K2 also works perfectly. It is a bit lengthy, but it has the advantage of automation. It needs no user intervention. The highlighter changes its position, as soon as the values in the cells are updated. In the IF formula, I replaced semicolons with commas.

Thanks once again. Both solutions are very helpful.

 

[Andrzejek]

I’m glad you found your solution.

You can easily automate these few steps I gave you with a little Macro.
Excel is very good in finding Min or Max value in a column.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.