View Full Version : Discrete Time Conversion Problem
DimGR
06-22-2022, 01:35 AM
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
06-22-2022, 01:57 AM
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
06-22-2022, 04:57 AM
VLookup() might be easier
29862
DimGR
06-22-2022, 06:33 AM
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
06-22-2022, 07:09 AM
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
06-22-2022, 08:42 AM
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
06-23-2022, 07:56 AM
if your data is in table you can create a function for it.
p45cal
06-23-2022, 08:54 AM
Excel with FILTER and SORT functions?
29870
results in:
29871
georgiboy
06-24-2022, 01:50 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.