Return Last Entry - Not Equal to Zero

[Kevinsid]

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

 

[Nebu]

Hi:

Using Power query, solution in green table.

Thanks

 

[Kevinsid]

Hi Nebu

Thank you for your reply, are you able to provide some details on how this works within Power Query.

Can this only be done using PQ is there no combination formula to achieve the same in excel.

 

[Nebu]

Hi:

This can be done using formulas or VBA as well , but power query is the easiest way and will work even if you add more data to it, you have to just refresh the green table. If you can to the edit window of Power query you will be able to see the steps which I have applied to achieve the results. I would recommend you to learn more on power query as this is a powerful tool for data transformation and easy to handle.

Thanks

 

[Kevinsid]

Thank you for the reply.

Can you please explain in more details the PQ formula, especially the -2

= Table.AddColumn(AddedIndex, "Consumption", each try if [Electricity Reading]>0 and [Name]=AddedIndex{[Index]-2}[Name] then [Electricity Reading]-AddedIndex{[Index]-2}[Electricity Reading] else 0 otherwise 0)

Also instead of just returning the consumption can it also return in another column the previous reading.

Your assistance is most appreciated

 

[Nebu]

Hi:

-2 is to return the previous row, I used the part " [Electricity Reading]-AddedIndex{[Index]-2}[Electricity Reading] " to calculate consumption if you simply give AddedIndex{[Index]-2}[Electricity Reading] it will give you previous row reading.

Thanks

 

[Kevinsid]

Just t make sure that i am correct, the formula will go back to the previous row that does not equal zero, is that correct?

Can we modify to also bring through the actual previous reading and the consumption as it doing now

 

[Nebu]

Yep, one of the steps in there is filtering out all the record with 0 reading and the if condition will also take care of this.

 

[Kevinsid]

Hi Nebu

Please could you assist, I have followed all of your steps to the T and your solution worked perfectly, however when the name contains numbers and text the sort goes wrong, to overcome this I created another key sort both both Alpha and numerical and tried everything i know to get them to sort as text, but no such luck. Please could you review and advise where i am going wrong.

I have attached the sample file for references under name should sort correct, but they do not.

 

[Kevinsid]

Hi Nebu

Please ignore previous post, I realized that my issue was the order in which I has sorted the columns. When I sorted them correctly 98% of the results are now correct, However there are some dates that are not sorting correctly, I have tried everything I can think of, text to columns to try and force the date in case it was not entered as a date, format all dates as dates, etc. Nothing seems to change the sort.

I am desperate as I am 98% of the way there, I just cant seem to work out why the sort on the date is not working correctly.

Your help would be most appreciated

Kevin

 

[Nebu]

Hi:

I have fixed it, your sorting were off hence you were getting wrong results.

Thanks

 

[Kevinsid]

Hi Nebu

The date sorts correctly but everything else no longer works, meaning that the previous reading and consumption on each of the names is no longer working.

Not sure if you remember the original thread/question, as per May 16 above

Please could you advise.

 

[Nebu]

Hi:

Find the attached.

Thanks

 

[Juniad]

You could use this formula as well, with special keys Ctrl + Shft + Enter
=INDEX($C$2:$C$14,MATCH(1,($A$2:$A$14=F2)*($B$2:$B$14=G2)*($C$2:$C$14<>0),0))