extracting and printing cells under a condition from a large data

Originally Posted by yellowcanary

Hello,

I have a data table on excel. It is attached. (Book1) Pa and Pb are my probability values and I want to extract the highest value for the combination of each Pa and Pb. The highest values are under I column, namely Best. I have a table on the right and for example, at the left up corner I want excel to print the Best value where where Pb=0.01 and Pa=0, and so on. What is the easy way to do it?

To make certain I understand the first question: Start with copy and paste this array-entered formula in cell N6. Fill down and across to BA45.

Formula:

Please Login or Register  to view this content.

Array formulas are not committed in the regular way. You commit by pressing and holding Ctrl + Shift as you hit Enter. You will know that it has been entered successfully when you can see curly braces {} around your formula in the formula bar. You do not type these in yourself. Excel does it for you.

Does this give expected results for the first question?

Yes, I did it now. It works. Thank you so much. As you can see now, I have triangle. Since I assume Pa<Pb, just ignore the -3.6 after Pb=0.01 and Pa=0.
I have to do numeric analysis with a large data and it will help me a lot. Now, I want to distinguish the P1,P2,P3,P4. Is there a way to put a color to each cell depending on their value. Now, I have only P1 and P2 in this table, but when I change the probabilities P3 and P4 will be included. I want to show each cell with different colors, so I can see the trend for each P. I attached the excel file again.

Originally Posted by yellowcanary

Thank you. Is there a way move these colors to the table? I need to see where the color changes from one probability to another. Also, in this example I have only 2 colors but sometimes I need to show all P's in 4 colors. So, using the table to see it would be more convenient.

When you say table are you referring now to cells N6:BA45?


Edit If that is what you are referring to I cannot think of a way to do that formula-wise without your workbook taking an enormous performance hit. VBA might be an alternative, but I know very little VBA. I can call for help on this one if you like. Please let us know.

Not having followed this thread much, please test this and see if it is giving the same results.

1. I added a helper column in K, (which can be hidden), and used this in K2 (the Table copied it down)...
=Table1[[#This Row],[Pa]]&" "&Table1[[#This Row],[Pb]]

2. I adjusted FT's ARRAY formula from
=IFERROR(INDEX($I$2:$I$821,MATCH(MAX(($B$2:$B$821=N$5)*($A$2:$A$821=$M6)),($B$2:$B$821=N$5)*($A$2:$A$821=$M6),0)),"")

to this regular formula
=IFERROR(INDEX($I$2:$I$821,MATCH(N$5&" "&$M6,$K$2:$K$821,0)),"")

As far as the colors are concerned, can you provide a few samples of what you want?