Time/Frequency/Data Calculation with Array Table

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

Select allOpen in new window

Q-28703272.xlsm

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.

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.

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.

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.

:) 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.

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.

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

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.

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

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.

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 :)

If you liked that one, you'll love this one!

https://www.experts-exchange.com/questions/28704811/Changing-Formats-with-Macro.html

B.

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.

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

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.

:) The remaining 10% is bluffing (such as the ability to drink coffee whilst looking busy).