Log In
Register
Home
Active Member
Xtreme Pivot Tables
Excel for Finance
May 26, 2016
Offline
Please help
I have a sheet which controls electricity readings that are entered with a date and a corresponding name (Kevin, Simon, Mark etc) as shown in the attached spreadsheet. There may be instances when the readings are taken for a particular month that we cannot obtain the reading and therefore no reading is take and zero is entered. I need a formula that will look at the previous reading (not equal to zero) and return the reading as the last reading , which consumption from the current reading to the last reading is calculated.
You would notice in the sample sheet that the previous two readings may be equal to zero therefore the formula has to ignore these reading and go find the reading before that was not equal to zero.
Any assistance to provide such a formula would be most appreciated
if anything is unclear please ask and i will do my best to provide further clarity
Kevin
July 16, 2010
Offline
Hi Kevin,
That's quite a conundrum! Please see solution attached which uses an INDEX MATCH array formula of sorts:
=IF(OR(G5=0,H5=""),0,INDEX($G$4:G4,MAX(IF(($F$4:F4=F5)*($G$4:G4<>0),ROW($A$1:A1)))))
I took inspiration from this post on finding the last value in a range.
Mynda
1 Guest(s)
