Sleeper: Stumped.....Phase calculator

[waterboy]

Greetings all,

Greenie here and at VBA. Would be indebted for some advice on a problem...

I have two worksheets with dates. I apply a formula/algorithm based on the agreement/lack thereof of these dates in worksheet 1 and 2, however the formula changes with time. I've attempted code, but haven't had success, so I've given up and used copy/paste

Ex.) (basically, i'm calculating phase [0-360 deg] over time
Worksheet 1 (W1)

Reference date:time
1 12/31/00 18:08
2 1/1/01 6:50
3 1/1/01 18:38
4 1/2/01 6:44

Worksheet 2 (W2)

Date:time of observ. Phase
1 1/1/01 6:15 343.28
2 1/1/01 6:30 350.36
3 1/1/01 6:45 357.45
4 1/1/01 7:15 12.51

Phase is calculated based on the reference date:time in worksheet 1, if the date is greater in worksheet 1 than 2, this triggers a change in the algorithm to reference the next later date in worksheet 1, so no calculated phase is greater than 360 degrees.
The algorithm is 360*(W2 [time1] - W1 [time1])/(W1 [time2]-W1 [time1])

What I can't figure out is how to apply the algorithm to the cells in W2, such that it will change correctly (using a counter, I presume) with time.

[johnske]

Hi waterboy, welcome to VBAX,

I'm afraid that there's not really enough information supplied for anyone to give an answer yet. For instance, how are these dates/times obtained - are they to be typed in as 'raw data' or are they obtained from some preceding formula?

Also, are you wanting a hard-coded VBA solution with maybe a Worksheet_Calculate or Worksheet_Change event as a solution to this? Or is a 'soft-coded' spreadsheet solution more preferable?

More needs to be known about the algorithm you've given - what you have is 360*(W2 [time1] - W1 [time1])/(W1 [time2]-W1 [time1])

This can be written as 360(A-B)/(C-B) where
A=W2 [time1]
B=W1 [time1]
C=W1 [time2]

If you want the phase to be less than or equal to 360, this means that C must always be greater than or equal to A, and C cannot equal B.

i.e. Phase = 360(A-B)/(C-B), C >= A and C <> B

So we have to test for both these conditions before anything is evaluated

In VBA code this loosely translates to

If C >= A And C <> B Then Phase = 360*(A-B)/(C-B)

Or, in Worksheet code

=IF(AND(C>=A, C<>B),360*(A-B)/(C-B),"")

But what about negative phases? Are these meant to be considered? E.G. If we assume that B will always be less than or greater than both A and C the answer will always be positive, but for A < B < C the phase will be negative...

More information is really required before anyone can help you.

Regards,
John

[waterboy]

My apologies, allow me to provide you with a more specific example:

Place these date_time in a worksheet named: Phase
12/31/00 18:081/1/01 6:501/1/01 18:381/2/01 6:441/2/01 19:081/3/01 7:56
Place these dates in Sheet2
1/1/01 0:151/1/01 0:301/1/01 0:451/1/01 1:151/1/01 1:301/1/01 1:451/1/01 2:151/1/01 2:301/1/01 2:451/1/01 3:151/1/01 3:301/1/01 3:451/1/01 4:151/1/01 4:301/1/01 4:451/1/01 5:151/1/01 5:301/1/01 5:451/1/01 6:151/1/01 6:301/1/01 6:451/1/01 7:151/1/01 7:301/1/01 7:451/1/01 8:151/1/01 8:301/1/01 8:451/1/01 9:151/1/01 9:301/1/01 9:451/1/01 10:151/1/01 10:301/1/01 10:451/1/01 11:151/1/01 11:301/1/01 11:451/1/01 12:151/1/01 12:301/1/01 12:451/1/01 13:151/1/01 13:301/1/01 13:451/1/01 14:151/1/01 14:301/1/01 14:451/1/01 15:151/1/01 15:301/1/01 15:451/1/01 16:151/1/01 16:301/1/01 16:451/1/01 17:151/1/01 17:301/1/01 17:451/1/01 18:151/1/01 18:301/1/01 18:451/1/01 19:151/1/01 19:301/1/01 19:451/1/01 20:151/1/01 20:301/1/01 20:451/1/01 21:151/1/01 21:301/1/01 21:451/1/01 22:151/1/01 22:301/1/01 22:451/1/01 23:151/1/01 23:301/1/01 23:451/2/01 0:151/2/01 0:301/2/01 0:451/2/01 1:151/2/01 1:301/2/01 1:451/2/01 2:151/2/01 2:301/2/01 2:451/2/01 3:151/2/01 3:301/2/01 3:451/2/01 4:151/2/01 4:301/2/01 4:451/2/01 5:151/2/01 5:301/2/01 5:451/2/01 6:151/2/01 6:301/2/01 6:45

Activate R1C2 in Sheet2

Run the following, which calculates the phase for three sets of dates from the Phase worksheet, but there must be a much more efficient method, as I have years of this information...? (i.e. could you assist me with converting this to more efficient loop?:

Sub Phaser() 
Do While Not ActiveCell.Offset(-1, 0) = 0
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]>Phase!R2C[-1],0,(RC[-1]-Phase!R1C[-1])/(Phase!R2C[-1]-Phase!R1C[-1])*360)"
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(-1, 0).FormulaR1C1 = _
"=IF(RC[-1]>Phase!R3C[-1],0,(RC[-1]-Phase!R2C[-1])/(Phase!R3C[-1]-Phase!R2C[-1])*360)"
Do While Not ActiveCell.Offset(-1, 0) = 0
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]>Phase!R3C[-1],0,(RC[-1]-Phase!R2C[-1])/(Phase!R3C[-1]-Phase!R2C[-1])*360)"
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(-1, 0).FormulaR1C1 = _
"=IF(RC[-1]>Phase!R4C[-1],0,(RC[-1]-Phase!R3C[-1])/(Phase!R4C[-1]-Phase!R3C[-1])*360)"
Do While Not ActiveCell.Offset(-1, 0) = 0
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]>Phase!R4C[-1],0,(RC[-1]-Phase!R3C[-1])/(Phase!R4C[-1]-Phase!R3C[-1])*360)"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

[mdmackillop]

Hi Waterboy,
Welcome to VBAX.
If you select your code section and click on the VBA button, it will format as above. If you have some sample data and/or code, you can zip the file and attach it to your post. You'll see this option by clicking on the Go Advanced button below the Message box, and clicking Manage Attachments. Remember to remove any sensitive data, email addresses etc. first.

[johnske]

Hi waterboy,

yes, as MD suggested above, it's probably best for you to give a zipped example for someone to work on (there's always someone ready to jump in and help )

For instance, from your preliminary post I had assumed that there would be the same amount of entries on each sheet and wrote some preliminary code based on this assumption (and the assumption that this would be a "new" book with maybe a 'few' entries and not an existing one containing many entries) where-as from your last post I can see this appears not to be the case and my code would fail to do the job you want...

Regards,
John