Distributing data on a matrix

[Eyas Hawashin]

Hi, I will appreciate all the help I can get on the attached file. Thanks in advance.

Eyas

 

[AliGW]

... but how about a job that got 5 complexity and 4 effect for example, will it be placed in box 5 or 6?

I'm going to make this a bit easier (or maybe harder!) for you: if YOU can't work out what the results should be, you won't be able to programme Excel to do it for you. It doesn't do fuzzy logic, so you are going to need to give it rules by which it can determine where those entries should go.

Here's another bit of help: using phrases like "it is clear" is not advisable, as it may not be at all clear to those trying to help you. You have to explain WHY it is clear so that others understand your thought processes.

I need excel to place them in the 9 box matrix using a scientifc approach.

Precisely!

I hope this helps.

 

[GraH - Guido]

A simple scatter may do?

 

[Hui]

Excel can do the math

But

You need to define the rules!

I'd suggest a helper Column

D2: =(1+INT((B2-1)/3))*(1+INT((C2-1)/3)) copy down

That will tell you which box each goes into according to your matrix

 

[Hui]

Then add columns for X and Y values
then chart them

I have added random numbers to the X & Y values so they aren't all on top of each other
Even though a Job will shift it stays in the same 1 - 9 box


But hey I don't have any rules to tell me what not to do!

 

[Eyas Hawashin]

Excel can do the math

But

You need to define the rules!

I'd suggest a helper Column

D2: =(1+INT((B2-1)/3))*(1+INT((C2-1)/3)) copy down

That will tell you which box each goes into according to your matrix

Thanks a lot, I tried your formula, the result exceeded 9, the maximum box number should be 9. Please try on the sheet to see. Can you please explain your formula?

 

[bosco_yip]

1] Adopt Hui's box's number formula from post #.4 in Col D

2] See attached file with 2 helper columns in Col D and Col E of which are hidden by custom format together with distributing data inside 9 boxes

Regards
Bosco

 

[GraH - Guido]

2] See attached file with 2 helper columns in Col D and Col E of which are hidden by custom format together with distributing data inside 9 boxes

Cool vlookup! I'd wish I came up with that one

 

[Eyas Hawashin]

Cool vlookup! I'd wish I came up with that one

Thanks so very much bosco-yip, this is terrific, But can you please explain the formulas in Columns D and E.

 

[GraH - Guido]

Haha, Bosco's formulas are often out there. But the one in D is by Hui's hand.

That will tell you which box each goes into according to your matrix

I know too little of even simple maths like this to come up with such things myself, but using the formula Evaluate function in Excel helps me in decoding. You could try that yourself.

The vlookup by Bosco in E is a joy...
Vlookup only returns the 1st value found for a matching search value. This vlookup is used in the 9 boxes to make the "chart".
So... in E the very first time the matrix value shows up, E must already contain all matching job positions. Therefore he looks up the same matrix value in the rest of column E. See this relative reference A2 & IFERROR(", "&VLOOKUP(H2,H3:I$52,2,0),"")
To make the result string it is concatenated "&" with the job position reference in A2.
Iferror is used to replace "N/A" values which vlookup returns when there is no exact match. The replace value is nothing. In fact this vlookup is calculated "bottom up".

 

[Eyas Hawashin]

Haha, Bosco's formulas are often out there. But the one in D is by Hui's hand.

I know too little of even simple maths like this to come up with such things myself, but using the formula Evaluate function in Excel helps me in decoding. You could try that yourself.

The vlookup by Bosco in E is a joy...
Vlookup only returns the 1st value found for a matching search value. This vlookup is used in the 9 boxes to make the "chart".
So... in E the very first time the matrix value shows up, E must already contain all matching job positions. Therefore he looks up the same matrix value in the rest of column E. See this relative reference A2 & IFERROR(", "&VLOOKUP(H2,H3:I$52,2,0),"")
To make the result string it is concatenated "&" with the job position reference in A2.
Iferror is used to replace "N/A" values which vlookup returns when there is no exact match. The replace value is nothing. In fact this vlookup is calculated "bottom up".

OMG. Thanks so much for both of you. Okay, I created a new and fresh sheet and defined the rules. Can you please enter the right formulas so that I understand the thing. The new sheet is simple, I am sure it will be a 5 minutes job of your valuable time. Thanks a lot.

 

[r2c2]

@Eyas Hawashin

I have adopted the excellent VLOOKUP by @bosco_yip to create this. I have not bothered filling the last sheet. This is easily achieved by another set of lookups. May be you should take this up as a learning task and write the formulas.

All said and done, I think this is a lousy way to present such data. Given the fact that half of the matrix is empty and few cells are over-crowded, a simple table with color coding and sorting could do a better job of explaining how tricky each position is.

 

[Eyas Hawashin]

Thanks so very much for everyone who helped me, I highly appreciate it.

 

[Eyas Hawashin]

Haha, Bosco's formulas are often out there. But the one in D is by Hui's hand.

I know too little of even simple maths like this to come up with such things myself, but using the formula Evaluate function in Excel helps me in decoding. You could try that yourself.

The vlookup by Bosco in E is a joy...
Vlookup only returns the 1st value found for a matching search value. This vlookup is used in the 9 boxes to make the "chart".
So... in E the very first time the matrix value shows up, E must already contain all matching job positions. Therefore he looks up the same matrix value in the rest of column E. See this relative reference A2 & IFERROR(", "&VLOOKUP(H2,H3:I$52,2,0),"")
To make the result string it is concatenated "&" with the job position reference in A2.
Iferror is used to replace "N/A" values which vlookup returns when there is no exact match. The replace value is nothing. In fact this vlookup is calculated "bottom up".

Haha, Bosco's formulas are often out there. But the one in D is by Hui's hand.

I know too little of even simple maths like this to come up with such things myself, but using the formula Evaluate function in Excel helps me in decoding. You could try that yourself.

The vlookup by Bosco in E is a joy...
Vlookup only returns the 1st value found for a matching search value. This vlookup is used in the 9 boxes to make the "chart".
So... in E the very first time the matrix value shows up, E must already contain all matching job positions. Therefore he looks up the same matrix value in the rest of column E. See this relative reference A2 & IFERROR(", "&VLOOKUP(H2,H3:I$52,2,0),"")
To make the result string it is concatenated "&" with the job position reference in A2.
Iferror is used to replace "N/A" values which vlookup returns when there is no exact match. The replace value is nothing. In fact this vlookup is calculated "bottom up".

Thanks so much to you and to Bosco