Find next rank number based on criteria and reverse v lookup

[bartoni1]

Hi All,

I wonder if someone would know how to help with this dilemma. I have a table (see below) where i have ranked vehicles launched based on if they are based in either the US or UK. What Id like to do is find the difference in days between launch dates on the next ranked launch and previous ranbked alunch and next launch. So if a vehicle is ranked number 2 in launch dates (Mar-15) in the UK. I want to try and find difference (in days) in launch date of the vehicle ranked 1 (Jan-15) and vehcile ranked 3 (July-15).
I assume its a match offset but i cant quite figure it out.

Many Thanks

Country	Vehicle	Launch Date	Country Rank	Duration vs -1 Rank (Days)	Duration vs +1 Rank
USA	Car	Jan-15	2	-31	""
UK	Car	Jan-15	1	""	59
USA	Car	Dec-14	1	""	1
UK	Car	July-15	3	-122	""
UK	Car	Mar-15	2	-59	122

<tbody>
</tbody>

 

[Tetra201]

If:
- you have no duplicate range numbers for the same country, and
- your launch dates are real Excel dates formatted as "mmm-yy"

then try the following formulas:

=IFERROR(1/(1/SUMIFS(C:C,A:A,A2,D:D,D2-1))-C2,"")
=IFERROR(1/(1/SUMIFS(C:C,A:A,A2,D:D,D2+1))-C2,"")

 

[bartoni1]

Thats amazing. Thanks!!
What does this bit mean/ do? =IFERROR(1/(1

 

[Tetra201]

Thats amazing. Thanks!!
What does this bit mean/ do? =IFERROR(1/(1

You are welcome.

In simple words, this construct returns a zero-length sring ("") when the SUMIFS(...) returns a zero, that is when no match found.