Ice melt model in Excel
 

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

Ice melt model in Excel
 

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"thehigh_23" 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

Ice melt model in Excel
 

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: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=104466

Ice melt model in Excel
 

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


"Pecoflyer" wrote:


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: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=104466

Ice melt model in Excel
 

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


"Pecoflyer" wrote:


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/me...pecoflyer.html)
View this thread: 'Ice melt model in Excel - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=104466)




--
Pecoflyer

Cheers -
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=104466

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

Ice melt model in Excel
 

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" wrote in message
...

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_ Wrote:
;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


"Pecoflyer" wrote:
-

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/me...coflyer.html)-
View this thread: 'Ice melt model in Excel - The Code Cage
Forums'-
(http://www.thecodecage.com/forumz/sh...php?t=104466)-

--


--
Pecoflyer

Cheers -
------------------------------------------------------------------------
Pecoflyer's Profile:
http://www.thecodecage.com/forumz/member.php?userid=14
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=104466




--
thehigh_23