Complex convert time to minutes formula required

Bit of a complex one to explain but here goes.

In my worksheet cells G16:G28 are formatted as Custom hh:mm and users will therefore enter values of 01:40, 02:55 etc. However not all the cells within that range will be populated. Sometimes the last value will be entered in G20 other times it will be G27 or G22.

I then have another cell which I need to have find the last value entered in the range of G16:G28, convert that into minutes and then divide that by 79.

Any ideas how to do this as I can't work out a way of finding the last value and any time conversion formulas I apply don't seem to work at all. Apart from that it's all going perfectly!

Many thanks for that, very much appreciated. I am a bit confused probably because I really don't understand Excel very well.

If in say G18 I have a value of 05:00 than this should equal in minutes 300.

If I use the formula of =lookup(9.9999e307,G16:G28) I get 0.208333333 If I multiply that by 1440 I get the correct 300

However if I use the other formula of =(hour(lookup(9.9999e307,G16:G28)*60+minute(lookup(9.9999e307,G16:G28)))) I get 12. I don't really understand what this formula is doing. Sorry my knowledge of Excel is basic at best.

To my mind I just need to use the first formula but then multiply the result by 1440 and divide by 79. Only problem is that whenever I add anything else to that formula I just get syntax errors. Or am I missing something?

Ahh thanks very much. I had tried that with the first formula but got errors which I didn't understand. This works perfectly. Cheers all for your help!

@ peakoverload

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as an infrequent user of the forum, you may have forgotten (or may not be aware) that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Apologies to all. Have rectified this and will remember to do this in the future.

Sorry to re-open this thread but I've had to change something and now it doesn't work and I don't understand why.

I had to change the formula of =LOOKUP(1000,G16:G28)*1440/79 slightly because it caused a problem with another formula in another cell. I amended it to this =LOOKUP(1000,E8,G16:G28)*1440/79. In E8 I just enter a 0. The reason for this is that I have another checkbox which when ticked activates a conditional formatting which reveals the result of this formula along with some text. However if nothing was entered in G16:G28 it was coming up with a #N/A. By adding the zero it now simply shows 0 if nothing is entered in G16:G28.

That all worked but then I needed to move the cell with =LOOKUP(1000,E8,G16:G28)*1440/79 to another worksheet. I then set G16:G28 on the original worksheet as a named range called TimeLine and ammended the formula to:

=LOOKUP(1000,E8,TimeLine)*1440/79

E8 has also moved to the new worksheet

I now find that this only works if something is entered in G16, it appears to ignore G17:G28 entirely. I've no idea why this is happening. Anyone have any ideas?

Ahh okay I've found what the problem is. It's the addition of E8 in the formula and the 0 in the cell.

Is there a way to amend this formula then so that if nothing is entered in G16:G28 that it just returns nothing rather than #N/A

=LOOKUP(9.9999E+307,'Tracking Sheet'!G16:G28)*1440/79

Hey that's brilliant! I only have Excel 2003 unfortunately but your second formula works a charm. Thank you so much, I really appreciate it especially as I really couldn't figure out a way of doing this myself.