Choose maximum value out of a number of values that match certain criterias from an array

Hi All,

Been a member for over a year, many thanks to all, it's been an educational experience.

I am trying to write a formula that will do 2 steps:-

1- Choose several values out of an array according to several criteria
2- return the highest value amongst above chosen values

Example

Excel Forum.png

In this Example i want a formula to be entered in column "G" that will calculate the number of days between the last return date of the vehicle and the shipping date of the vehicle on this row.

In other words the formula in "G5" must give the result "2" since the vehicle 22 returned from its last ship on January 4th and then shipped again on the 6th, to me that's 2 days in standby, and "G8" must give the result "3" because the vehicle 23 last returned on Jan 12th and shipped again on the 15th.

Any suggestions?!!

If a formula can't calculate this, how can i rearrange the data tables so some sort of a formula can calculate the standby days accurately?

Please upload excel workbook not a picture. Nobody likes to retype data from the picture.
If needed change all private informations but leave what's important to understand problem.

Hello
try this in G2
=IFERROR(B2-INDEX($E$1:E1,MAX(IF(A2=$A$1:A1,ROW($A$1:A1),0))),"")
and copy down
This is an array formula to use this….. copy it from here…..then press F2 on the cell where you want the result.....cursor will appear....now paste the formula and press CTRL+SHIFT+ENTER instead of just enter as we usually do...

Tell me if it works right….

Hope it helps
Do ask for any other query you may have…

If you are satisfied then mark then PLEASE mark this thread as “SOLVED” (by going to thread tools at the top and clicking on “mark this thread as solved”) and you can just click on ADD REPUTATION below my post to say thanks...

Regards
Sourabh Gupta

Hi,

Sorry for the late reply and messed up thread, i thought a removed the extra images, they must be confusing.

I attached an excel sheet, the formula am looking for will have to be entered in column "H".
each row represents data relevant to a single shipment.
i need to calculate how many days every vehicle is idle after return date until the next shipping date.
Usually going down rows you will find trips that have been done later, but not as a rule, so i cant say it's in any type of order.

i tried the suggestions given, but i suppose because of the several screenshots attached "by me!!" it's not working, but am much closer to understanding the formula's to be used.


What does "desensitize" mean? i tried Google translate, i don't know how to remove "emotions" from my data?!!

awaiting more suggestions,

Thanks in advance

Thanks Vikas_Gautam your solution worked after changing it to

Formula:

Please Login or Register  to view this content.