formula to add one to previous dependent on another column

EmilyT — Wed, 03 Jan 2024 23:30:41 GMT

Hi all,

I know my subject is a little convoluted sounding, but I'm thinking this must be something fairly simple to do and I'm just not knowledgeable enough to know how...

I have attached an example of my data. I would like to write a formula for my "Daynight consecutive" column to have it populate the field with a unique number according to the date (in the "date" column) and whether it is day or night (in the "day or night" column). (This is for me to use in the calculations of "Average Day Temperature" and "Average Night Temperature"). The actual number that is in there doesn't actually matter. I just want a unique identifier for the the period of day or night for each date. But it isn't as simple as combining the "date" and "day or night" columns because the night period spans over two dates.

Hope that makes sense....the example file hopefully illustrates it. I've manually entered the values in "Daynight consecutive" but would be really helpful if I could use a formula to create that column.

Thank you!

Re: formula to add one to previous dependent on another column

Jordan_Hiller — Wed, 03 Jan 2024 23:55:11 GMT

If you can rely on the row order always being sorted by time, this would work:

Modified dataset attached.

Re: formula to add one to previous dependent on another column

EmilyT — Thu, 04 Jan 2024 21:55:56 GMT

Thank you! This worked great and has saved me lots of time!

topic Re: formula to add one to previous dependent on another column in Discussions

formula to add one to previous dependent on another column

Re: formula to add one to previous dependent on another column

Re: formula to add one to previous dependent on another column