1

I want to calculate the weighted average of temperatures of the nearest weather stations around a city. I have data on the latitude, longitude, altitude and temperature of some stations. Mostly, I'm trying to find the formula used by the weatherspark website, so i'm using the real exemple from the image below, but i'm having issues with the formula, as it computes the Altitude for the weighted average of the individual contributions (Joinville city 68% contibuition and Curitiba city 32% contibuition).

enter image description here

Pontal do Paraná city : latitude -25.674°, longitude -48.511° and 7 m above sea level;

Joinville city : latitude -26.304°, longitude -48.846° e 4 m above sea level;

Curitiba city : latitude -25.428°, longitude -49.273° e 910 m above sea level;

*Joinville: 42 miles = 68 km; -10 ft = -3 m

*Curitiba 43 miles = 69 km; 2,963 ft = 903 m

*values from the image show miles and ft.

The image below shows my excel sheet: enter image description here

Does anyone have an idea?

Thank you for any kind of help!

Frederico Montzel
  • 29
  • 4
  • 1
    What have you tried and what, exactly, is the problem you are facing with your attempt? – cybernetic.nomad Jul 25 '23 at 19:46
  • You are on the right track. Your calculation in `Y2` is the correct calculation of the weighted average of the values in cells `Q2` and `V2` using the weights in cells `S2` and `X2`. These weights are the inverses of the distance values in cells `P2` and `U2` (though your "explanations" of the weight calculations in cells `S3` and `X3` are in error.) Now all you need to do is perform the same weighted average calculations for the 36/39 pairs showing in your worksheet image. **Hint:** find out about relative and absolute cell references in Excel - it will make your task much easier. – DMM Jul 26 '23 at 19:27

0 Answers0