Ice melt model in Excel

  • Thread starter Thread starter thehigh_23
  • Start date Start date
T

thehigh_23

Hi
For a project I am doing university I need to produce a basic ice melt
model. I have got hourly temperature data for a whole year from a
certain location and area data from a glacier I am looking at, which
has been broken down to several elevation bands each containing the
area of ice present in that band.
Basically I need the model first to calculate the sum of postive
degrees for each day of the year (so this would add together all the
temperature data above 0 for each day). This would leave me with 365
postive degree day factors (DDF).

Each of these DDF's then need to be multiplied by a Meltfactor (say
0.06) and then multiplied by the area of a selceted elevation band
(DDF x 0.06) x Area

I am still a bit of a novice when it comes to all things excel so Im
struggling a bit with this one. Its been suggested that I should use
VBA but to be honest i dont know where to start. Any useful tips etc
would be much appreciated.

Cheers
Ryan
 
From the information you've given so far, I see no need to use VBA. Assuming
your temperatures are in column B, your DDF formula is
=SUMIF(B:B,">0")
You can then multiply this by your metlfactor and area (let's say they are
in cells C2 and D2 respectively)
=SUMIF(B:B,">0")*C2*D2
 
Hi Pecoflyer,

The third argument of SUMIF is optional, if the 1st and 3rd ranges are the
same you can ignore the 3rd argument.
 
Hi Guys

Thanks for your help so far, much appreciated. The formula works a
treat. However the only problem i have is with that formula i would
have to calculate the sum of positive degrees for each day individually
(because I need 365 indvidual positive degree sums) which could take
some time, and i may have to repeat the process for more years later
on.
So with the =SUMIF(F1:F24,">0",F1:F24) formula when i drag the result
down the next box changes to =SUMIF(F2:F25,">0",F2:F25)
Is there a way of telling excel to jump to next set of 24 hours when
you drag the result down e.g the box underneath would contain
=SUMIF(F25:F48,''>0'',F25:48) and so on?

Thanks again
Ryan






'Pecoflyer[_353_ said:
;842804']Thx for the reminder Shane

Shane Devenshire;373589 Wrote: -
Hi Pecoflyer,

The third argument of SUMIF is optional, if the 1st and 3rd ranges are
the
same you can ignore the 3rd argument.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


:
- day- ice-- containing-- all-- with-- so-- should-- tips--
------------------------------------------------------------------------- Pecoflyer'- Forums'-
Click to expand...


--
Pecoflyer

Cheers -
------------------------------------------------------------------------
Pecoflyer's Profile:
http://www.thecodecage.com/forumz/member.php?userid=14
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=104466
Click to expand...
 
Hi Ryan

try
=SUMIF(INDEX(F:F,(ROW()-1)*24+1):
INDEX(F:F,(ROW()-1)*24+24),">0")
and copy down
--
Regards
Roger Govier

thehigh_23 said:
Hi Guys

Thanks for your help so far, much appreciated. The formula works a
treat. However the only problem i have is with that formula i would
have to calculate the sum of positive degrees for each day individually
(because I need 365 indvidual positive degree sums) which could take
some time, and i may have to repeat the process for more years later
on.
So with the =SUMIF(F1:F24,">0",F1:F24) formula when i drag the result
down the next box changes to =SUMIF(F2:F25,">0",F2:F25)
Is there a way of telling excel to jump to next set of 24 hours when
you drag the result down e.g the box underneath would contain
=SUMIF(F25:F48,''>0'',F25:48) and so on?

Thanks again
Ryan






'Pecoflyer[_353_ said:
;842804']Thx for the reminder Shane

Shane Devenshire;373589 Wrote: -
Hi Pecoflyer,

The third argument of SUMIF is optional, if the 1st and 3rd ranges are
the
same you can ignore the 3rd argument.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


:
-

Hi,
supposing temp of deg is not included and your data for a given day-
is-
in cells A1 to A24, follwoing wll give the sum of daily positive-
temps-
:
=sumif(a1:a24,">0",a1:a24)
You ca then multiply this outcome with the meltfactor and area

thehigh_23;373222 Wrote:-
Hi
For a project I am doing university I need to produce a basic ice--
melt--
model. I have got hourly temperature data for a whole year from a
certain location and area data from a glacier I am looking at,--
which--
has been broken down to several elevation bands each containing--
the--
area of ice present in that band.
Basically I need the model first to calculate the sum of postive
degrees for each day of the year (so this would add together all--
the--
temperature data above 0 for each day). This would leave me with--
365--
postive degree day factors (DDF).

Each of these DDF's then need to be multiplied by a Meltfactor--
(say--
0.06) and then multiplied by the area of a selceted elevation band
(DDF x 0.06) x Area

I am still a bit of a novice when it comes to all things excel so--
Im--
struggling a bit with this one. Its been suggested that I should--
use--
VBA but to be honest i dont know where to start. Any useful tips--
etc--
would be much appreciated.

Cheers
Ryan




--
thehigh_23-


--
Pecoflyer

Cheers -
-
-------------------------------------------------------------------------
Pecoflyer's Profile: 'The Code Cage Forums - View Profile: Pecoflyer'-
(http://www.thecodecage.com/forumz/members/pecoflyer.html)-
View this thread: 'Ice melt model in Excel - The Code Cage Forums'-
(http://www.thecodecage.com/forumz/showthread.php?t=104466)-

--
Click to expand...


--
Pecoflyer

Cheers -
------------------------------------------------------------------------
Pecoflyer's Profile:
http://www.thecodecage.com/forumz/member.php?userid=14
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=104466
Click to expand...
Click to expand...
 
Back
Top