Identification of a specific formula/statistics function

Question

Level 1

4 points

Identification of a specific formula/statistics function

Good morning, I am asking you for a hand in identifying a formula that will allow me to calculate, within a Numbers file, the number of days with hot and cold waves. This file of mine is about the temperatures of some places and therefore there are so many statistical functions behind it. Among the many parameters that I calculate there is one that is the one that allows me to calculate the number of days with heat waves and cold waves; to realize this formula I had thought of counting every day with a temperature above 30°C, with the detail however, that, it must select only the days above 30° in a row though. (example, if on July 15th the maximum is 31°C, on July 16th it is 32°C and on July 17th it is 29 ̊C, then the function must return me a value of 0, since the heat wave lasted only two days, So too little to be considered a heat wave. Instead, if on July 15 the maximum is 31 ̊C, on July 16 it is 32°C and on July 17 of 34°C, then you must return me value 3). I want to apply the same principle to calculate the number of days with cold waves, but just change the temperature, which obviously is not 30°, but maybe I'll wear it that it must be -5°C). Thank you guys.

MacBook Air 13″, macOS 14.5

Posted on Jun 23, 2024 6:41 AM

Reply

Answer 1

Yellowbox

Level 7

22,003 points

Jun 23, 2024 11:52 PM in response to Yuri2302

Hi Yuri,

Perhaps a table like this will work for you.

Columns A, B, C are data

Formula in D2 is IF(B2≥30,"Hot","")

In E2 and E3, the are no data for the last 3 days. I left them blank (I inserted no formulas).

Formula in E4. We can combine the functions IF AND like this

IF(AND(D2="Hot",D3="Hot",D4="Hot"),"Heat wave","")

The function AND considers all conditions, accepts all that are TRUE and returns the answer FALSE if not all are TRUE.

For example, formula in D7 is IF(AND(D5="Hot",D6="Hot",D7="Hot"),"Heat wave","")

Similarly, in Column F, we can't use Rows 2 and 3 (not enough preceding data).

Formula in F4 is IF(C4≤−5,"Cold","")

Formula in G4 is IF(AND(F2="Cold",F3="Cold",F4="Cold"),"Cold wave","")

I am using >= (greater than or equal to) and <= to (less than or equal to). Your limits may differ.

Regards,

Ian.

Reply

Answer 2

Badunit

Level 7

31,152 points

Jun 24, 2024 4:46 AM in response to Yuri2302

In one column you can mark the heat wave days. You can then sum them up.

Starting with cell D4,

D4 =IF(OR(AND(C2>30,C3>30,C4>30),AND(C3>30,C4>30,C5>30),AND(C4>30,C5>30,C6>30)),1,0)

Fill down to the end of the column then fill up to row 2

In row 2 you will have to remove the first and second AND(...)

In row 3 you will have to remove the first AND(...)

In the second to last row you'll have to remove the last AND(...)

In the last row you'll have to remove the last two AND(...)

The formula looks at the current temp and the two rows above, one row above and one below, and two rows below to see if any of those will make a string of 3 consecutive days. If so then the result is a 1, otherwise a 0. This works fine in the middle of the table but, at the top and bottom, it is looking at nonexistent or header/footer rows so you have to edit the formula to remove those invalid AND statements. To be clear, though, there is insufficient data in the top and bottom rows to know for sure if they are or are not "heat wave" days.

Reply

Answer 3

Yuri2302 Author

Level 1

4 points

Jun 24, 2024 12:33 AM in response to Yellowbox

Good morning and thanks for answering me. The structure of my file does not allow me a clear understanding of the example you gave me, that's why I now attach photos of how my file is structured so that you can understand and explain it to me better. Thank you.

In column D I have inserted the formula that assigns the word "hot" if the maximum temperature is higher than 30°C. (I will then hide column D).

As you can also see in the first photo, my file consists of several sheets: the first is the one in the photo where all the daily temperatures are written; then the following sheets contain the summaries and the monthly averages (see second photo; look at cell AG1)

And finally, the last sheet contains the summary and the annual average for each parameter. I hope it's clearer now, thank you.

Reply