Flexible Cloud Cover Estimating based on Latitude and Longitudes

[teoxu]

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

[Pete_UK]

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

[tim201110]

not clear what y want?

[teoxu]

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

[Pete_UK]

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

[MrShorty]

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?

[teoxu]

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

[Pete_UK]

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