Using furthest down Cell for math statements

I have printer toner usage sheet that has dates in a column I want it to reference the furthest one down to complete its job. I've attached an example excel file of what I've to to make it work but its dirty and doesn't work properly if the value changes to 100%, if I change the cartridge. For the most part what I have works but its terrible and difficult to alter.

What I'm trying to get it to do is find the average Page Per Day printer from the top value OR if column C has a cell with 100%. Additionally, I need it to references the furthest cell down that isn't 0 or nothing to find out which date to use to figure replace by date and the toner empty date. I'm hoping for a more elegant solution that i'm currently using that is accurate even if the toner level goes up.

Try:

=LOOKUP(2,1/(C19:C42<>0),B19:B42)

Can you give examples of what kind of results you expect based on which conditions?

It needs to find the Pages Per Day using the average from "pages left" column and must check if the last value(furthest down) isn't greater than the cell above it.

After that it should be easy to fill in the rest of the blanks.

To get the average PPD use:

=AVERAGEIF(G19:G42,"<>0")

I am still unsure how you want to determine the Replace by date?

You mean?

=(LOOKUP(2,1/(G19:G42<>0),G19:G42)-200)/K21

Originally Posted by NBVC

You mean?

=(LOOKUP(2,1/(G19:G42<>0),G19:G42)-200)/K21

Correct, I didn't have my sheet on me at the time so i couldn't make the exact equation! Also, thanks for writing that for me because I hadn't got to it yet.

If you are satisfied with the solution provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

The last time what was equal to K24 or G19?

Please elaborate what you are looking for...

Do you mean?

=(IF(ISNUMBER(MATCH(K24,G19:G42,0)),K24,G19)-(LOOKUP(2,1/(C19:C42<>0),G19:G42)))/((LOOKUP(2,1/(C19:C42<>0),B19:B42)-IF(ISNUMBER(MATCH(K23,B19:B42,0)),K23,B19)))

So instead of K23 you want it to look at column B for when 100% last occurred... and if did not occur, then use B19. Is that right?

Perhaps:

=(IF(ISNUMBER(MATCH(K24,G19:G42,0)),K24,G19)-(LOOKUP(2,1/(C19:C42<>0),G19:G42)))/((LOOKUP(2,1/(C19:C42<>0),B19:B42)-IF(ISNUMBER(MATCH(100%,C19:C42,0)),LOOKUP(2,1/(C19:C42=100%),B19:B42),B19)))