Discrete Time Conversion Problem
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.
Maybe a formula:
B2:B5 = Your volumes=LARGE(B2:B5,COUNTIF(A2:A5,">"&E4)+1)
A2:A5 = Your times
E4 = the date you want to check against
VLookup() might be easier
Capture.JPG
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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:
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?
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
Modified formula would be:
=LARGE(C1:C8,COUNTIFS(B1:B8,">"&F4,A1:A8,F3)+1)
I have learned how to add screenshots now!
Screenshot 2022-06-22 150800.png
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...
if your data is in table you can create a function for it.
Excel with FILTER and SORT functions?
2022-06-23_165059.jpg
results in:
2022-06-23_165351.jpg
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Maybe an array formula could work if you do not have access to the Fileter & Sort functions:
This is an array formula so would need to be entered with CTL + SHIFT + ENTER=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))
Posting Permissions
Reply With Quote
