Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 Quote:
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting an Excel Model into a Function | Excel Worksheet Functions | |||
How do I model heat transfer using Excel? | Excel Discussion (Misc queries) | |||
Excel question for financial model | Excel Discussion (Misc queries) | |||
how do i model discounts in excel solver | Excel Worksheet Functions | |||
any hint for using excel to create a model? | Excel Discussion (Misc queries) |