+ Reply to Thread
Results 1 to 8 of 8

Flexible Cloud Cover Estimating based on Latitude and Longitudes

  1. #1
    Registered User
    Join Date
    04-29-2015
    Location
    Edinburgh, UK
    MS-Off Ver
    Microsoft Office 2013
    Posts
    19

    Flexible Cloud Cover Estimating based on Latitude and Longitudes

    Hi all,

    I currently have global cloud fraction datasets, basically cloud cover data for the entire earth at 0.1 degrees resolution for both latitude and longitude in CSV Excel format. I need to create a spreadsheet which allows me the flexibly and, more importantly quickly, ascertain the AVERAGE cloud cover fraction for any pairs of latitude and longitude values (i.e.: the area being estimated should be a square, bounded area).

    As these annual datasets are large (about 60mb/year), I have only included a single year with this thread. However, within this dataset, I have also had to remove large chunks of latitudes and longitudes to further reduce the size so I can attach it here. The principles still stay the same though!

    The rows represents longitude and the columns represent latitude. No need to worry too much about the positive or negative longitude and latitude values.

    Would appreciate if anyone has any ideas - thank you very much!

    Best wishes,
    Xu Teo
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,613

    Re: Flexible Cloud Cover Estimating based on Latitude and Longitudes

    What kind of thing do you envisage - a pair of drop-downs for latitude and for longitude, where you can define a From and To range for each, and then the average of the numbers within that range is evaluated? Presumably 99999 represents a null value - it would be better if these were blanks.

    Pete

  3. #3
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Flexible Cloud Cover Estimating based on Latitude and Longitudes

    not clear what y want?

  4. #4
    Registered User
    Join Date
    04-29-2015
    Location
    Edinburgh, UK
    MS-Off Ver
    Microsoft Office 2013
    Posts
    19

    Re: Flexible Cloud Cover Estimating based on Latitude and Longitudes

    Hi Pete,

    Thanks for your reply and sorry for not being a little more clear with what I would like for my final result. Yes, I would like a pair of drop-down menus for each pair of latitudes and each pair of longitudes as you have mentioned. After which, from those constraints, calculate the average value of the bounded area. However, as you have pointed out as well, 99999 represents a null value and should therefore be excluded. Unfortunately, these the null values came original with the dataset I only just downloaded.

    Thank you so much for your help.

    Best wishes,
    Xu

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,613

    Re: Flexible Cloud Cover Estimating based on Latitude and Longitudes

    First of all, you can get rid of the null values quite easily. When you open the file, do CTRL-A to select all, then CTRL-H for Find and Replace and:

    Find what: 99999
    Replace with: leave blank
    Click Replace All

    Then you will need to insert a new sheet on which you can select the ranges. I'll describe this in a later post (time for some lunch now).

    Hope this helps.

    Pete

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,349

    Re: Flexible Cloud Cover Estimating based on Latitude and Longitudes

    Is there a specific part of programming this that you are having trouble with? Here's how I think I would approach this [note that I am assuming a basic arithmetic average =sum(xi)/count(xi)]:

    1) Enter desired lat/lon limits into 4 cells.
    2) In an convenient column adjacent to the data, use a SUMIFS() function to sum that rows data inside of an IF() function to determine if this row's data should be included in the average. =if(and(lat2<=a1,a1<=lat1),sumifs(this row,row1,greater than long1,row1,less than long2,this row, <10),false)
    3) A similar function in an adjacent column using countifs() to count the number of entries that went into the sums.
    4) The arithmetic average would then be =sum(sumifs column)/sum(countifs column).

    Does that make sense? What part do you get stuck on?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  7. #7
    Registered User
    Join Date
    04-29-2015
    Location
    Edinburgh, UK
    MS-Off Ver
    Microsoft Office 2013
    Posts
    19

    Re: Flexible Cloud Cover Estimating based on Latitude and Longitudes

    Quote Originally Posted by Pete_UK View Post
    First of all, you can get rid of the null values quite easily. When you open the file, do CTRL-A to select all, then CTRL-H for Find and Replace and:

    Find what: 99999
    Replace with: leave blank
    Click Replace All

    Then you will need to insert a new sheet on which you can select the ranges. I'll describe this in a later post (time for some lunch now).

    Hope this helps.

    Pete
    Hi Pete_UK,

    Many thanks for that. Was just wondering if you have any further ideas/comments regarding what I mentioned above?

    Thanks again!

    Xu

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,613

    Re: Flexible Cloud Cover Estimating based on Latitude and Longitudes

    Well, MrShorty gave you some pointers in Post #6 as to how to proceed with this. Did you not follow what he was saying?

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 09-28-2015, 07:10 AM
  2. Excel 2010 - 2013 cloud based Macros in O-365
    By Coleman34 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-16-2014, 11:48 AM
  3. VBA to repeat the process of calculation
    By vanitarathod in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-26-2014, 08:16 AM
  4. [SOLVED] Use of 'flexible' formula based on cell value?
    By Eric_25 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-22-2014, 10:29 AM
  5. [SOLVED] Lookup values based on longitude and latitude
    By MattRNR in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-05-2014, 06:18 PM
  6. Excel PowerPivot Add-in? + Cloud based data sources.
    By niceguy21 in forum Excel General
    Replies: 5
    Last Post: 01-02-2013, 11:06 AM
  7. Word Cloud (Working but want to tweak to adjust font color based on frequency and...)
    By VTHokie11 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-29-2011, 03:23 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1