Excel: Trying to (Approximately) Model Parametric Growth (and Decay) Curves 2

[racookpe1978]

Working in Excel, am modestly familiar with the equations, but will not pretend to be an expert. Am certainly NOT a programmer in that system!

I have a number of different regions I need to model to approximate ice area as a function of day-of-year, but if I can get an answer for one, I think the rest can be figured out.

Each area stays at maximum for a different length of time, so the curves are not symmetric.

Freeze durations do NOT = melt durations, so each side of the area curve is not the same, and is not symmetric.
I think that modeling each half of the freeze-melt cycle as a different cosine or sine function is adequate, unless somebody has a better approximation they know of. No known "real" data is available.

Each region begins "growing" (freezing) at a different day-of-year (ranges from 0 to -60)
Call it DOY_FS variable name, area at start of freezing is always 0.0

Each stops growing (completely freezes) at a different day-of-year (ranges from 2 to 60)
Call this date DOY_FE variable name.

Area at end of freezing is different for each region, call it A_Max

Melt side of the problem:
Each starts to melt at a different date, call this one DOY_MS ranges from 120 to 150
Each ends melting back at area = 0.0, at DOY_ME. ranges from 190 to 215.

So the freeze_length = the difference between the freeze start and freeze end dates.
And the melt length = difference between melt start and melt end dates.

If I assume that the freezing is more-or-less like half of a sine function, then freeze_length = half of the "period" for that sine wave, right?
Starts at 0.0, ends at A_max
Melt_length is half of the "period" of the cosine wave going down, again shifted up by A/2
Starts at A_max, ends at 0.0

And total height of that sine wave = Area_Max, shifted up from -A/2 to 0.0 ?

Then I have a constant area from freeze end to melt start DOY_FE to DOY_MS
But, I'm getting stuck at translating that into "excelese" properly for the periods. (Probably my radians conversion into dates and periods.)

So, the basic wave is:
A freeze = (A_max * sin(DOY/(2*pi*freeze_length)+A_max/2) And I have to adjust it for the actual freeze start date.
A melt = (A_max * cos(DOY/(2*pi*melt_length) -A_max/2) And I have to adjust it for actual melt start date.

 

[racookpe1978]

No, I can see by curving fitting some test eqtns ..

For the general y = A + B (cos(C(x) + D)))
for area = 0.0 to (assume A = 100)
for DOY = 0 to 120 (for 120 days = freezing time)
Period = 240 (if symmetric)

Then Area = A/2 + A/2 * (cos (2*pi/P * DOY) + "D"))
Area = 50 + 50 * (cos(0.026166 DOY) + D)

So "D" = -3.0 because I need to phase shift the cos curve by -D/C or -(-3/0.026166) = 120 days??

 

[Sparweb]

Maybe I'm not reading carefully but I think you want to model this as a natural logarithm, not a sine/cosine.
It just seems more appropriate to your problem. I don't see the any reason for the pattern of melting and freezing to be symmetric.
Are you plotting measurements you have taken of a frozen surface, or are you trying to build a model to predict it?

I've solved this kind of problem (rate changing with time) iteratively before. I started by creating columns with all of the immediate input data, where the first row has the initial conditions of the system, and the next row is updated after the calculations find the changed state of the system. Successive columns to the right solve each changing parameter - changing over a period of time which is also set before you start, to some convenient value. Then the next line puts the results of this last iteration back in the first calculations, and the same equations in the columns to the right then proceed to calculate the next iteration. At this point you should be set up to "copy-fill-down" as many rows as you need for your system to complete its evolution. You may find it helpful to have a group of initial conditions declared in a bunch of cells at the top of the sheet, to be read before Excel moves down to lower cells. Excel calculates left-to-right, top-to-bottom. Use this to put the calculations in the correct order. Using Named Cells also helps with values that are constant so you don't have to remember things like "Cell B4 means the ambient temperature which is 25 degrees Celsius". Just name the cell "T_amb" and all your other formulas can use that Name instead of "B4".

This project is not what Excel was designed for, and it's a lot of work to build and troubleshoot.

There is a much simpler way to do this, and it also has the advantage of showing your work in algebra (the way your good high school teachers wrote algebra).
Download Mathcad for free from PTC, write the algebra follow a few online examples to get the syntax right, and you'll be able to produce graphs and such stuff you need to make a fairly nice looking presentation.


STF

 

[racookpe1978]

The daily record (and plot) of areas for each region used to be available on-line, but the University that maintained them stopped all updates in April 2016 when the satellite sensor crashed. So, the plots are available, but are now a default straight line. No information at all.

I am scaling from the old plots, which show a gradual increase as freezing starts, then a rapid increase (near linear) through the middle of the freeze cycle, then a gradual decrease to a plateau. So, much like a sine/cos wave than the logarithmic decline as in radioactive decay, heat loss to a constant state, or a solutions' concentration. Yes, I am approximating the curve of each region, but I think that approximating the five regions will be adequate. Certainly better than a step increase from zero to 100%, and the linear function I had tried.

 

[IDS]

I have set up a spreadsheet (in Excel!) which I think does what you want.

For the freezing zone you need to generate a number between -Pi/2 and +Pi/2, in proportion to the number of days since the start of freezing, get the Sin of that number, add 1 and divide by two, to get a factor between 0 and 1 to apply to the total area:

(SIN((DOY-F_Mid)/F_Days*PI())+1)/2*A_max

You then need to wrap that in a couple of Ifs to check if the day is before or after the freezing period:

=(IF(DOY<DOY_FS,-1,IF(DOY>DOY_FE,1,SIN((DOY-F_Mid)/F_Days*PI())))+1)/2*A_max

I have used range names to make the formula readable, but if you set this up on another spreadsheet you should use cell references to specify mixed fixed/absolute addresses, that will still work when copied down and across:

=(IF($C16<D$5,-1,IF($C16>D$6,1,SIN(($C16-D$11)/D$10*PI())))+1)/2*D$7

The melting zone needs to be adjusted because we want the Cos between 0 and Pi:

=(IF($C41<D$8,1,IF($C41>D$9,-1,COS(($C41-D$8)/D$12*PI())))+1)/2*D$7

See the screenshot below and the attached file for more details.

Regarding whether Excel is the right tool for the job, it might not be what a spreadsheet was designed for, but in my opinion spreadsheets are just fine for applications of this type. It's taken me longer to type this message than it did to set up the spreadsheet.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IRstuff]

I was thinking that a sigmoid function would be more suited for your application, but Excel apparently still does not have one. This article

http://www.keksecks.de/indianalytics/?p=41

discusses how to implement one in a spreadsheet.

I'm thinking the main advantage is that the sigmoid as implemented in the article is fully continuous, while the trig function approach is not, making the sigmoid ever so slightly easier to deal with.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[racookpe1978]

Thank you! Have copied and am editing the sample spreadsheet. I understand I need to make four sets - C and D expanding A & B in the sample set.

 

[IFRs]

This thread demonstrates the awesome talent given away on this site.

 

[IDS]

See:

https://newtonexcelbach.wordpress.com/2017/06/21/plotting-freeze-thaw-data/

for updated spreadsheet complete with functions to generate both sin/cosine and sigmoid curves.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/