Array transformation and sinusoidal distribution of data macro

Dear all,
Have one problem, need to transport daily values of data (transpiration) to hourly data. I have more than 1000 days, so I need some quick way to do it.


Daily Variation.jpg

Colum A represent days, colum B data, so in column E there is hour and column F data, so at least I need to expand each value from column B to 24 column (for each hour) and divide the values by 24. Also the variation of transpiration have to have sinusoidal distribution over the day. Here is the procedure:

Variations in the potential transpiration rate during the day needs to be be generated using the assumption that hourly values of the potential transpiration rate between 0-6 a.m. and 18-24 p.m. represent 1% of the total daily value and that it has a sinusoidal shape during the rest of the day as follows:

V.jpg

So please can someone help me with this, note that I have a lot of data so the "manual" way it's just too long.

Please ask me if the post is understandable. Not a native speaker.

Thank you
Regards

Originally Posted by mrice

This might help

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.

Thank You guys very much, but also have some problem not sure if the formula was wrong or anything else, but I think if you make an little change it will work nice, so the problem is that the value in column B represent the daily value of the ET so when you use the formula you should get the sum value of first one the for instance 0.11 - e.g. when you sum all the values of 24 hours you should get 0.11 please see the picture:


Attachment 266923

To tehneXus; thank you very much, yes the formula was not right one, it was just an example for you guys, but it works with your module, just one question, why does it not work with the 18:00 h?


To mrice: thank you for the details.
Very usefull.
Thank you

Originally Posted by tehneXus

Hi,

The reason why its not possible to use the formula with 18:00 is:
1. The formula is only valid for t (0,263d; 0,736d) which is 6:20 to 17:40

2. Using a t outside of this range causes the 'sin' part to be 0

With the formula you provided I don't see a possibility to sum the values and get the 0,11.

Oh, thank you very much, but I have to deal somehow with this, there is three solutions, can you please wright me the macro for at least one of it:

1. to set values from 1:00 till 6:00 am and from 18:00 till 0:00 pm as zero values and that during the rest the values are distributed sinusoidal with the max at 12:00 am and sum of daily value e.g. 0.11
2. to set up the sinusoidal distribution trough whole day 01:00 till 0.00
3. just to set up hourly data which will be divided /24 like for 0.11 = 0.004583

Can you help me with this code, I never used MACROs and VBAs so even a easy code is difficult to wright to me, so it would save me a lot of time.

Thank you in advance

Originally Posted by Vilim4

Oh, thank you very much, but I have to deal somehow with this, there is three solutions, can you please wright me the macro for at least one of it:

1. to set values from 1:00 till 6:00 am and from 18:00 till 0:00 pm as zero values and that during the rest the values are distributed sinusoidal with the max at 12:00 am and sum of daily value e.g. 0.11
2. to set up the sinusoidal distribution trough whole day 01:00 till 0.00
3. just to set up hourly data which will be divided /24 like for 0.11 = 0.004583

Can you help me with this code, I never used MACROs and VBAs so even a easy code is difficult to wright to me, so it would save me a lot of time.

Thank you in advance

Dear, I have manage to rearange equation and to find the calculation for normal distribution: please see atach picture, there is a coefficient with wich the value should be multiply every hour. At the end the sum value correspond to the daily e.g. 0.11.

Untitled.jpg

So would it be to much truble just to put that in code.

Thank you
Regards