Showing most current data...

[bartinpc]

We are using an hourly updated spreadsheet to help keep track of plant emissions. It is manually updated hourly by the operator.

There are four columns...
"A" column lists the 24 hours in the day.
"B" column is the hourly emission rate for that hour.
"C" column calculates our newly projected limit for that day.
"D" column calculates the daily rolling average.

I want a separate cell to list the most current projected limit. Thus, I've been trying to use the NOW() function to determine the current time, then go down to the proper row and look up the cooresponding data. I've been trying to use the MATCH function with the NOW function inside it to get the proper row with no success.

I'm not sure that I'm going about this in the most efficient manner. I've blanked out any cells that do not have data for their hour, and tried to have it search for no text, but didn't know how to get it to back up one for the most recent data. I'm not sure that makes sense as I read what I just typed.

Any help or advice would be greatly appreciated.

[duane]

I would use =countif(b2:b100,">0) to determine number of data points

(I will assume your data starts in row 2)

then

=offset(c1,countif(b2:b100,">0"),0) will be the last calculated number in column C

if you need, you could put in error protection for any case where no value in column B is >0

=if(max(b2:b100=0,0,offset(d1,countif(b2:b100,">0"),0))

I do assume there will be no hourly rate<0

[duane]

another approach would be to use

=rounddown((now()-today()),0)*24-1 to determine how many hours
of data for today you will have, and then match this number in column a
to find your data point.

[bartinpc]

Thank you, I'll give this a try tomorrow while at work, and report my results then. Thank you for the quick response.

[bartinpc]

Thanks duane!!

I'm still not 100% sure how that formula works, but it has worked like a charm. I'm going to do some more studying of the help file for the countif and offset formula, so I can understand it all.

Again thanks.