Find next rank number based on criteria and reverse v lookup

bartoni1

New Member
Joined
Jan 18, 2017
Messages
5
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


CountryVehicleLaunch DateCountry RankDuration vs -1 Rank (Days)Duration vs +1 Rank
USACarJan-152-31""
UKCarJan-151""59
USACarDec-141""1
UKCarJuly-153-122""
UKCarMar-152-59122

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,631
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,"")
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,631
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,719
Messages
5,597,733
Members
414,170
Latest member
Mdm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top