Discrete Time Conversion Problem

[DimGR]

I have a table similar to the one bellow:



Time

Volume Produced



2022-06-22 00:00:00

0



2022-06-22 05:00:00

100



2022-06-22 10:00:00

180



2022-06-22 15:00:00

500




Is there any formula or VBA transformation that can tell me how much volume was produced in any time between the ones in the table, taking the volume Produced value of the previous time?

For example, at 2022-06-22 09:00:00, produced volume would be 100 (taking the value of 2022-06-22 05:00:00).

Thank you in advance.

[georgiboy]

Maybe a formula:

=LARGE(B2:B5,COUNTIF(A2:A5,">"&E4)+1)

B2:B5 = Your volumes
A2:A5 = Your times
E4 = the date you want to check against

[Paul_Hossler]

VLookup() might be easier

29862

[DimGR]

Thank you for the response! Both solutions work! :)

Just to make it slightly more difficult, what if the range (in the previous example A2:B5) is not known, but determined by a key in column A.
Like below:


Key

Time
Volume Produced


KeyA

6/22/2022 12:00:00 AM
1



KeyA

6/22/2022 5:00:00 AM
5



KeyA
6/22/2022 10:00:00 AM
7



KeyA
6/22/2022 3:00:00 PM

9



KeyB

6/22/2022 12:00:00 AM
0


KeyB

6/22/2022 5:00:00 AM
100


KeyB
6/22/2022 10:00:00 AM
180


KeyB
6/22/2022 3:00:00 PM
500




In this example, the desired key is "KeyB", resulting in produced volume of 100 at 9AM, instead of 5 that key "KeyA" would result in. So is there any way to get the string "A6:A9" that corresponds to key b and then use the formulas above?

[georgiboy]

Modified formula would be:

=LARGE(C1:C8,COUNTIFS(B1:B8,">"&F4,A1:A8,F3)+1)


I have learned how to add screenshots now!
29863

[DimGR]

Thanks for the update. The only problem is that this is working only when the values are sorted according to time from earlier to latest...

[arnelgp]

if your data is in table you can create a function for it.

[p45cal]

Excel with FILTER and SORT functions?

29870

results in:

29871

[georgiboy]

Maybe an array formula could work if you do not have access to the Fileter & Sort functions:

=INDEX($C$2:$C$9,MATCH(MAX(IF($A$2:$A$9=F4,IF($B$2:$B$9 < F5,$B$2:$B$9))),IF($A$2:$A$9=F4,IF($B$2:$B$9 < F5,$B$2:$B$9)),0))

This is an array formula so would need to be entered with CTL + SHIFT + ENTER