Find the oldest and newest value for each item and calculate the ratio

**henr** · 02-03-2019, 07:50 PM

Hi guys,
I'm trying to find a magic formula which could find the oldest and newest value for each project item and calculate current oldest to newest index. The formula should be elastic to serve for future months when added to raw data. Alos, usually my data is sorted by month not by project name (if it's important).
I tried to play with combination of VLOOKUP, MAX/MIN but I couldn't get values for individual projects.

Attached is a simplified spreadsheet for your reference.

Appreciate any help!

**Bo_Ry** · 02-03-2019, 10:17 PM

Please try at

P3 and press Ctrl+Shift+Enter
=O3/INDEX($O$3:$O$10,MATCH(MIN(IF($B$3:$B$10=B3,$A$3:$A$10)),IF($B$3:$B$10=B3,$A$3:$A$10),))

or

=O3/INDEX($O$3:$O$10,MATCH(AGGREGATE(15,6,$A$3:$A$10/($B$3:$B$10=B3),1),INDEX($A$3:$A$10/($B$3:$B$10=B3),),))

**henr** · 02-04-2019, 12:37 AM

Initially had a problem with ?NAME errors but then realised some project names were missing. When I fixed them both your formula worked perfectly. Thanks Bo_Ry!

Find the oldest and newest value for each item and calculate the ratio

LinkBack

Thread Tools

Rate This Thread

Display

Find the oldest and newest value for each item and calculate the ratio

Re: Find the oldest and newest value for each item and calculate the ratio

Re: Find the oldest and newest value for each item and calculate the ratio

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Find oldest and newest date by grouped material in 2 columns...

[SOLVED] kEEP NEWEST ROWS AND DELETE OLDEST ONE IF

Filter not working when sheet's protected

[SOLVED] Sort dates in column from oldest to newest...

Arranging Pivot table data from Newest to oldest to Newest does not appear

Top ten oldest and top ten newest

oldest to newest

Bookmarks

Bookmarks

Posting Permissions