What formula can I use to identify gaps in my temperature records?

Hello, I have a Numbers file where I write the temperatures and on them I calculate a multitude of parameters; in particular, I am looking for a formula that allows me to see, on a basis of 365 days or a calendar year, how many days the temperature data are missing. Thanks to this formula I can understand if the results of a certain location can be given good or not. I was thinking of a similar thing: look at the photo.

(In the first photo look at the AV2 cell). In the second photo it is understood that the formula I am looking for, and that returns that value to me, must draw the data in the first sheet where there are all the temperatures from January 1 to December 31.


To make you understand better, if for example, on 10 days of minimum temperatures, two days of minimum temperature values are missing, then the formula must return this to me: "2/10", because there are two days of missing data out of 10; so since my base is always that of a year, that is 365, I would like the formula to return me "2/365". thank you in advance.

MacBook Air 13″, macOS 14.5

Posted on Jul 3, 2024 4:21 AM

Reply
4 replies

Jul 6, 2024 2:56 PM in response to Yuri2302

I will give you formulas in US English and make an attempt to translate them to Italiano. There is a numbers function countif() which will count only when the values in a range meet a condition.


In your case, I think, you want to count those days where the cell is empty.


The function for this is (assuming the temperature data is in column B):

=countif(B, "")



to make this indicate the "fraction" of days out of a year with 365 days you could do something like this:

=countif(B, "")&"/365"


In Italian:

=CONTA.SE(B; "")&"/365"



I hope this helps

Jul 7, 2024 4:39 AM in response to Wayne Contello

Good morning, thank you very much. The formula works like this, but now we have to move on to the next step and that is:

Since I write the temperatures every day and as of today July 7 it has been 189 days since the beginning of the year, that formula was applied not on a fixed basis of 365 days, but on a mobile basis of a certain day of the year, so in this case today's formula should be "/189", And as I write the temperatures every day, this mobile basis changes. Is it possible? Thank you.

Jul 7, 2024 10:13 AM in response to Wayne Contello

So the formula doesn't work, and also I forgot to specify that I use this formula inside a sheet that is different from the one from which the formula draws the data (that is, the sheet in which I write the temperatures daily). So I don't know if this thing I didn't specify can change the proper functioning of the formula.

That's why the formula doesn't work, I just don't understand anything lol.

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

What formula can I use to identify gaps in my temperature records?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.