I am a beginner to programs like Google Sheets or Excel and am currently trying to create a spreadsheet that keeps tally of average temperature and weather data in various cities. It updates daily. It looks like the attached picture right now. I am using SyncWith to get API data from Openweathermap.
Ideally, I want to have two more columns on the right side with a statistic for each city:
The average max temperature of the city. This means that it would take the average of the current value in the main.temp_max cell and average it with the value that replaces it. For example, if the max temperature is 23.52 and the next time it updates the max is 24, it would take the average of the two as 23.76 and put that value in the new cell to the right. Then the next day, if the max was 22.5, it would take an average with 23.76 and replace the average with 23.13, and so on.
The number of days a certain kind of weather appears. For example, every time there is any rain, it would add a +1 to the currently-existing value. Overall, it would be a tally of the number of rainy days there are. If possible, I would want to include various different weather.ids for the different degrees of rain. That is to say, 500 (light rain), 502 (heavy intensity rain), and 501 (moderate rain), for example, would all count for a +1.
Can this be done?