Function/solution to fill in cells!

Wow, puzzle!
Love it :-)


Well, you have whole set of formulas in first square. I've changed them (you had 49 similar formulas, but each one different - changed manually) to one universal one to be placed in E2 and copied down/right:

Formula:

Please Login or Register  to view this content.

This is used in original location (E2:K8)

Let me explain it a bit:
So in E2 INDIRECT evaluates content of D2 and it is compared with 9-2 so with 7
in E3 INDIRECT still evaluates content of D2, but it is compared with 9-3 so with 6, etc
in F2 INDIRECT evaluates content of D3 (because argument for COLUMN is now C1, so third column), and the result is compared with 7 (9-2), and so on, and so forth
This way we have just one formula good for all cells :-)


To have marked (filled 1) squares like these yellow in column E one need such formula checking if we are "above place for black square in our column and below square in next right":

Formula:

Please Login or Register  to view this content.

so conjunction of 2 very similar formulas, but one with reference to current column and second to next to right
to see them in attached file look at Af2:AL8

now lets take care of situation with location above black square in current column and below one in previous to the left (see it working in AN2:AT8):

Formula:

Please Login or Register  to view this content.

So we have all parts ready and can combine them into universal formula, used in M2 and copied down/right to S8:

Formula:

Please Login or Register  to view this content.

thx kaper, I will try this your solution now and get back to you... stewart

In the formula you now do not refer to column D but to P, row is no longer between 2 and 8 (9- something) but between 2 and 11 so 12-something. For row part of address you need to still refer to 1st, 2nd and 3rd column, so:

Formula:

Please Login or Register  to view this content.

and copy right/down

Note that shape is different than you sketched, because it is based on column P (and before D) while you draw your charts based all the time on column A - may be your normalization in column P is different than the one you used in column D? But it is out of topic. In this thread we focus on filling cells.
See my sketches (charts below yours - based on resp. columns D and P.

PS. I expect that proper formula for P2 (and copy down) is:

Formula:

Please Login or Register  to view this content.

you may first try it on D2 and copy down:

Formula:

Please Login or Register  to view this content.

Now you analyze data from column D, rows 10:16 (so 8 rows down from previous location. You have to adjust columns used to generate address from A, B, C to columns 8 right, so I,J,K as well as adjust how rows are calculated so instead of 9-ROW() you have 8 more: 17-ROW()

E10 and copy down:

Formula:

Please Login or Register  to view this content.

Unfortunately, you have to work it out and understand how the formula works, because otherwise, you will not be able to adjust it yourself.
To make this process easier, in my first post I've shown you 3 separate parts how formula is created.

of course you still have some more options:
1) someone else gives different solution
2) you rearrange data used to "draw" the "pixel charts" to horizontal. This way INDIRECT will not be necessary, making formula easier to read. If you use oposite ranking than is used now (largest value will receive lowest rank, part with ROW() can be a bit easier too hbeing just row(some cell) not somenumber-row()
3) you use the formula with manual adjustments similar as you did originaly - so de facto 49 separate formulas in E10:K16 do E10:

Formula:

Please Login or Register  to view this content.

E11:

Formula:

Please Login or Register  to view this content.

and so on down, while F10:

Formula:

Please Login or Register  to view this content.

and so on, and so forth

all fixed now...

thanks again.... should have it working soon....

I was now able to use your new formula please check out sheet with profiles (right).

Can you see any other way to make it more simpler?

thx again stewart

Please tell us what you have tried based on the techniques you have learnt already in this thread. It's the same procedure, which is why all your other threads were closed.