For array geeks= rankif ?

Joel --

Care to provide a 15 rows sample data? If so inclined, activate an empty cell, type =, select 15 rows of data (including labels), go to the Formula Bar, hit F9, copy what you see, and paste it in the follow-up.

Aladin

=========

Aladin, you always reply surprisingly quickly... I suspect that you're an hyperintelligent bot programmed to ask to any question in less than 2 hours. Well thanks for your help. Here's the data:

The array (disguised example) is {"Country","Product","Price";"A","x",1;"A","y",2;"B","x",34;"B","y",12;"B","z",54;"C","x",91;"C","y",51;"C","z",43}

Country Product Price
A x 1
A y 2
B x 34
B y 12
B z 54
C x 91
C y 51
C z 43

What I would like to get is for example
(B,x)->2 (B,y)->3 (B,z)->1
(C,x)->1

Thanks again,

Joel

Aladin: by the way, your F9 tip is AWESOME!

Rankif, version 1

Joel --

Consider what follows as version 1, although I'm not sure whether there will be another version just consisting of a single formula!

Lets say that A1:C9 houses the sample data.

In E1:F2 enter:

{"Country","Product";"B","x"}

In G2 array-enter: =SUM(IF((B2:B9=F2)*(A2:A9=E2),RANK(H2:H9,H2:H9)))

In H2 enter: =IF(B2=$F$2,C2,0) [ copy down as far as needed ]

I didn't carry out an extensive test; So I'm sure it will hold long...

Caveat. A product is listed just once per country.

Note. In order to array-enter a formula, hit control+shift+enter at the same time, not just enter.

Aladin

================= --

Re: Rankif, version 1

Thanks Aladin,

I thought about something like this, but this solution requires too much 'scrap space'. For one pair country/product, it's fine, but if I want to calculate the rank on each row of the database, I would need to add as many columns as possible countries...

Thanks anyway,

Joel --

Re: Rankif, version 2

Repost--Re: Rankif, version 2

Joel --

It seems I got a single formula formulation:

Referring to my earlier setup,

in G2 array-enter: =MATCH(INDEX($C$2:$C$9,MATCH(E2&F2,$A$2:$A$9&$B$2:$B$9,0)),LARGE(IF($B$2:$B$9=F2,$C$2:$C$9),ROW(INDIRECT("1:"&COUNTIF($B$2:$B$9,F2)))),0)

Copy down this array-formula to as many rows as the Country & Paroduct pairs in E and F that you have.

Although I'm confident it meets the specs, please test it thoroughly.

Aladin

========== Thanks Aladin, I thought about something like this, but this solution requires too much 'scrap space'. For one pair country/product, it's fine, but if I want to calculate the rank on each row of the database, I would need to add as many columns as possible countries... Thanks anyway, -- : Consider what follows as version 1, although I'm not sure whether there will be another version just consisting of a single formula! : Lets say that A1:C9 houses the sample data. : In E1:F2 enter: : {"Country","Product";"B","x"} : In G2 array-enter: =SUM(IF((B2:B9=F2)*(A2:A9=E2),RANK(H2:H9,H2:H9))) : In H2 enter: =IF(B2=$F$2,C2,0) [ copy down as far as needed ] : I didn't carry out an extensive test; So I'm sure it will hold long... : Caveat. A product is listed just once per country. : Note. In order to array-enter a formula, hit control+shift+enter at the same time, not just enter. : =================

Re: Repost--Re: Rankif, version 2

I am not sure it works, when I type C and z in E2 and F2, I get 2 instead of 3... I haven't tried yet to reverse engineer your formula, but I'll try to do it very soon

It actually works

Actually it works, but in the wrong order: it looks, for a particular product, what rank the country is. It's quite simple I guess to invert the two conditions.

Thanks Aladin