Time/Frequency/Data Calculation with Array Table

EE Pros,

I am trying to build an Array that relates different elements of time (Sec., Min., Day, Month, Year) so that if you select "an INTERVAL, you get the relationship applied against a selected "Start Date/Time" to produce an "End Date/Time" and by also selecting a FREQUENCY Measure, you will get the number of data points collected in that Time Frame.

I have posted a mocked up version with comments for  your review.

Thank you in advance,

B.

TIME-FREQUENCY-ARRAY.xlsm
Bright01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi B,

Are you opposed to using a Visual Basic for Applications [VBA] code routine to address part of your requirements?

For example, with the code below (& within the attached workbook), I placed the following formula within cell [C9]:

=dblDateAdd(LEFT(C6,1),C5,C4)

This formula returns a date (& time) as expected, I think.  Please can you confirm?


For cell [C10], is this just simply locating the "Frequency Measure" (cell [C7]) in the range [B17:B23], then multiplying the corresponding numeric value in column [A] with the value of "# of intervals" in cell [C5]?

If so, the following formula within cell [C10] will do that:

=IFERROR(INDEX(A17:A23,MATCH(C7,B17:B23,0)),0)*C5


The code, placed within a (Public/Global) code module ("basQ_28703272") within the workbook, is as follows:

Option Explicit
Public Function dblDateAdd(ByVal strInterval As String, _
                           ByVal dblNumber As Double, _
                           ByVal datValue As Date) As Double
                        
' -------------------------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/questions/28703272/Time-Frequency-Data-Calculation-with-Array-Table.html ]
'
' Question Channel: Experts Exchange > Questions > Time/Frequency/Data Calculation with Array Table
' Topic Area:       [ http://www.experts-exchange.com/topics/ms-excel/ ]
'
' ID:               Q_28703272
' Question Title:   Time/Frequency/Data Calculation with Array Table
' Question Dated:   2015-08-05 10:31 PM
' Question Asker:   Bright01
' Asker Profile:    [ http://www.experts-exchange.com/members/Bright01.html ]
' Attachment:       [ http://filedb.experts-exchange.com/incoming/2015/08_w32/927278/TIME-FREQUENCY-ARRAY.xlsm ]
'
' Solution posted:  6 August 2015 by fanpages
' Expert Profile:   [ http://www.experts-exchange.com/members/fanpages.html ] | [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2015 Clearlogic Concepts (UK) Limited                                            [ http://NigelLee.info ]
' -------------------------------------------------------------------------------------------------------------------------------
  
  Dim dblReturn                                         As Double
  
  On Error GoTo Err_dblDateAdd
  
  dblReturn = CDbl(datValue + (1462# * ActiveWorkbook.Date1904))    ' 1462 = (365 * 4) + 2
  dblReturn = DateAdd(strInterval, dblNumber, dblReturn)
  
Exit_dblDateAdd:

  dblDateAdd = dblReturn
  
  Exit Function
  
Err_dblDateAdd:

  On Error Resume Next
  
  dblReturn = 0#
  
  Resume Exit_dblDateAdd
  
End Function

Open in new window

Q-28703272.xlsm
Bright01Author Commented:
Fanpages,

Thank you!  I think you are on the right track; and I'm A-OK with using a Macro (VBA) rather then an Array and formulas.........

However, the numbers are not quite sequenced correctly.  Perhaps some definitions are in order;

1.) The Interval is the length of time that we are going to take measurements.  So if it is "2" and DAYs, then the length of the sample would be just that.  
2.) The Frequency is the sub or super set of how much data (data points) we are collecting WITHIN the Interval.  So if I selected "1" and "Days" (as the Interval Metric), and Frequency in "Hours", the result would be 24.  
3.) This should also work if I select Frequency as Weeks and the reading would be 1/7. For the interval of "1" and "Days" and the fact that our Frequency is actually in "Weeks".  

Finally, you have the Date/Time Stop cell (C9) correct.  It's the # of Data Points (C10) that is not showing up correctly.

I hope that is an easy and clear explanation.  Again, thank you for your assistance.

B.
Bright01Author Commented:
Fanpages,

I have been looking over this and have found only one error and that is in the formula in C10.  This may be the reason why I need an Array (or at least thought so).  When you select an Interval metric and a Frequency and number of Intervals, it is the intersection of the Interval value created by the selection of the Metric X number of Intervals and the relationship of the Frequency Measure that should produce the result for C10.  

So, for example, and in its simplest form, if you pick NUMBER OF INTERVALS = 1, and INTERVAL VALUE = Days, then Frequency Measure = Hours, you would get 24.  If you pick NUMBER OF INTERVALS = 1, and INTERVAL VALUE = Days, then Frequency Measure = MINUTES, you would get 1440 (i.e. 1 Day = 24 Hrs. X 60 MIN./HR = 1440.  And so on.    

Make sense?

Thanks,

B.
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

[ fanpages ]IT Services ConsultantCommented:
Hi B,

Yes, I think I see what you are trying to achieve.

I presume your previously provided table ("matrix"), "Time Relationship Array", needs to be updated to complete the missing entries.

Please can you do that with an updated attachment, & also offer some further sample input (differing variations of all three input parameters) together with their associated expected output?

I will then change the formula for cell [C10] to accommodate the results, proving the formula with your examples.

Thank you.
Bright01Author Commented:
Fanpages,

I don't know how to build out that array.  That was my original problem.  I started to but quickly realized I didn't know how to set it up.

B.
[ fanpages ]IT Services ConsultantCommented:
:) Ah, OK...

Can you please look at providing a few example input value combinations, with a range of differing values for...

[B5] # of Intervals
[B6] Interval Metric
[B7] Frequency Measure

...& your expected output within cell [C10] "# of Data Points"?

We can then either work on the table, or any other 'Expert' can then contribute to the solution if they wish to do so.

Thank you.
Bright01Author Commented:
Absolutely!

# of Intervals = 2
Interval Metric = Days
Frequency Measure = Minutes

This means that the scope or entire time of data collection is 2 Days, translated into number of minutes (as the data points collected) in those 2 Days.   So the math/array would calculate the relationship between the Day and the Minutes; so 60 X 24 = 1440 X 2 days = 2880 points of data in 2 days of time (Interval).

Another example;

# of Intervals 3
Interval Metric = Months
Frequency Measure = Days

So there are 7 days (may be fractional so it can be changed based on the value) in 1 week and 4 weeks in 1 month, so 7 X 4 = 28 X 3 = 84 Data Points in 3 months, with Data Points being in Days.

As you can see in an Array, we could calculate seconds in a multiple years if needed.  The Array should be able to be "tweaked" to more exact calculations based on more precision on the Time Values (e.g. Days in a month may be 30.3 and not 28).

Does that help?

B.
[ fanpages ]IT Services ConsultantCommented:
Yes, thank you.

I have attached an updated workbook.

The revised calculation in cell [C10] (based on the "Revised Time Relationship Array" I have included) is as follows:

=C5 * OFFSET($B$26,MATCH(LEFT(TRIM(C6),LEN(TRIM(C6))-1),$B$27:$B$33,0),MATCH(LEFT(TRIM(C7),LEN(TRIM(C7))-1),$C$26:$I$26,0))

Please can you re-review, & then advise if you think we now have what you were looking for?

Thanks.

(I have added some notes at pertinent areas within the workbook to show how the solution was reached)
Q-28703272b.xlsm
Bright01Author Commented:
Fanpages,

Thank you very much!  When I traveled on Sat. from Hong Kong to Beijing, I didn't realize that in Mainland China, Expert Exchange is not accessible.  Not sure why.  I'm linked to EE via my cell phone hot spot on a train traveling to XIAN and have a few minutes to download your fix and test it out.  I'll be back with you as soon as possible.  Your patience, capabilities and professionalism are greatly appreciated.

B.
Bright01Author Commented:
Fanpages,

I have added an example; and I'm not getting any results in C10.  Is there a way to complete the Array so that whatever Interval, Interval Metric and Frequency we choose, we can get the answer in C10?

I think we are close.  C9 is calculating correctly I believe.

B.
Time-Interval-Frequency-calculationv2.xl
[ fanpages ]IT Services ConsultantCommented:
Hi,

Assuming you saw the formula in cell [C10] I posted most recently did work within the workbook I attached, in your recent attachment you have changed the "Frequency Measure" (cell [C7]) drop-down list entries to state singular forms (that is, "Days" is now "Day", & "Months" is now "Month", & so on).  The "Interval Measure" (cell [C6]) entries) are also now singular (rather than in a plural form).

My previous formula was removing the "s" from the end of each entry to comply with the naming convention within the "[Revised] Time Relationship Array" headings.

Please change cell [C10] from:

=C5*OFFSET($B$15,MATCH(LEFT(TRIM(C6),LEN(TRIM(C6))-1),$B$16:$B$22,0),MATCH(LEFT(TRIM(C7),LEN(TRIM(C7))-1),$C$15:$I$15,0))

to

=C5*OFFSET($B$15,MATCH(C6,$B$16:$B$22,0),MATCH(C7,$C$15:$I$15,0))

I see the result of 42 in cell [C10] after making this change within your most recent attachment.

PS. The in-cell formulae within the range [F6:G11] that I added just to show how the result was reached, just for information, are now showing #N/A because of the amendments to the position of the "Revised Time Relationship Array" within your attachment.

Simply clear the contents of the range [F6:G11] if you are happy with the result in cell [C10].

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
PERFECT!  Thank you very much!  I'll have to study this to figure out how you did it with the Macro.  But this looks awesome.  Much thanks.  I will be building on this so I hope you will pick up the next "turn of the crank".

Again Fanpages,....... much thanks,

B.
[ fanpages ]IT Services ConsultantCommented:
No problem at all.

I like questions like this that take a bit of thinking & effort, rather than copy'n'pasting "standard" responses.

Hope to see you in another question soon :)
Bright01Author Commented:
[ fanpages ]IT Services ConsultantCommented:
Hi again,

I have just posted the following text in your other thread to draw your attention to a potential issue (that may well require resolution before you deploy this workbook in your user community):

---
...Usage in-cell is passing the first character of cell [C6] (as this will define the interval as "Second", "Minute", "Hour", "Day", "Week", "Month", or "Year"), the interval quantity value in cell [C5], & the starting date in cell [C4]:

=dblDateAdd(LEFT(C6,1),C5,C4)

This said, Bright01, thinking about this now, did you test the previous solution for both "Minute" & "Month"?

I think there may be an issue there (as both begin with "M").

I suggest you change cell [C9] from:
=dblDateAdd(LEFT(C6,1),C5,C4)

to:
=dblDateAdd(IF(C6="Minute","n",LEFT(C6,1)),C5,C4)
---

Please advise if you wish to discuss this further (here).

Thank you.
[ fanpages ]IT Services ConsultantCommented:
After further discussion (within the later question thread)...

Bright01:

Revised formula for cell [C9]...

Please change (the original formula):
=dblDateAdd(LEFT(C6,1),C5,C4)

or (the first revision mentioned above):
=dblDateAdd(IF(C6="Minute","n",LEFT(C6,1)),C5,C4)

to:
=dblDateAdd(IF(C6="Minute","n",IF(C6="Year","yyyy",LEFT(C6,1))),C5,C4)

Thank you.

An updated workbook is attached (based on my previous attachment).
Q-28703272c.xlsm
Bright01Author Commented:
Fanpages,

I substituted the new formula and will test it now.  At first glance.... it looks like it works well.

Thanks for the follow up.  I remember what a Senior Executive once told me, "90% of Leadership is follow up!".

B.
[ fanpages ]IT Services ConsultantCommented:
:) The remaining 10% is bluffing (such as the ability to drink coffee whilst looking busy).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.