Excel: Cell reference of column and current row | compounded cell referenecs

Hi Experts

My actual formula in cell K7 is this:
=HOUR(M7)+MINUTE(M7)/60
and here's K8:
=HOUR(M8)+MINUTE(M8)/60
and here's K9:
=HOUR(M9)+MINUTE(M9)/60

Isn't there an easy way to say
=HOUR(M + [ROW()] )+MINUTE(M + [ROW()] ) /60

Thanks
LVL 13
rfwoolfAsked:
Who is Participating?
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Hello rfwoolf,

Try:
=HOUR(INDIRECT("M" & ROW())) + MINUTE(INDIRECT("M" & ROW()))/60

Regards,

chris_bottomley
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
rfwoolf,

Though of course the formula drags down so not sure you would want it that way?

chris_bottomley
0
 
rfwoolfAuthor Commented:
Well it seems to work in Excel, but isn't working in a Google Document Spreadsheet. Do you know of another way of expressing it? Perhaps it doesn't like the word "INDIRECT" or maybe it's the & (ampersandt) ?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Chris BottomleySoftware Quality Lead EngineerCommented:
I wouldn't know anything about Google Spreadsheets ... i'm offended by Google Mail so wouldn't touch Google Apps, (because Mail is so light on automation features).

Chris
0
 
rfwoolfAuthor Commented:
Can you explain what you mean by light on automation features?
In any case, Gmail is a web-based email service in a landscape with Yahoo mail, hotmail/msn/live, and other webmails that are attached to email accounts, and as far as I'm concerned, in that landscape, Gmail is king. When was the last time you checked it out, and perhaps it does what you require but you just don't know how?
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Sorry just letting off steam whilst admitting my lack of knowledge with the apps.

FYI though I am used to being able to process a lot of mail through Outlook.  I actually logged mails arriving through outlook into my database and from my database I could restrict which emails I looked at via the filters etc in outlook.

None of that capability is supoorted by the web based entity that is google.  In theory you can synch Google with Outlook and still do the same in outlook but then that is using outlook as the app and Giggle as the provider therefore Google does not support automation in my terms.

Chris
0
 
Rory ArchibaldCommented:
Can I ask why you want this ROW() formula since, as Chris said, the row adjusts anyway as you copy the formula down?
It appears that if you use INDIRECT inside another function in Google Docs, it treats the returned value as text rather than a date.
0
 
barry houdiniCommented:
In excel you'd just copy that formula down the column, doesn't that work in google docs?
What sort of values do you have in column M, are you extracting the time from a time and date? If so you could use just
=MOD(M7,1)
although that would also include seconds, if any.....
regards, barry
0
 
rfwoolfAuthor Commented:
"Can I ask why you want this ROW() formula since, as Chris said, the row adjusts anyway as you copy the formula down?"
You're probably right, but as a programmer (not a an excel programmer I might add) dynamic values are much better than literals,
and in addition I wanted to learn how to concatenate cell references (using an & symbol perhaps as I note above);
if there was a way to tell excel, "For all cells in this range, give this dynamic formula" I would, and yes I know I can copy and paste, but heaven forbid someone inserts a row or something and the whole thing gets messed up.
0
 
Rory ArchibaldCommented:
There is a way to tell Excel that - use the formula you are using. If you use INDIRECT and ROW, you will get exactly the same result if you insert a row as you get with the formula you have, so I confess I don't see the point. I thought we were talking about Google Docs though? (same point applies to both, however).
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
>>> I thought we were talking about Google Docs

It has moved that way but the original question was limited to Excel of course.

Chris
0
 
barry houdiniCommented:
Re: my earlier post. Sorry that formula won't give you the result you need.
If you are converting a time value to a decimal it's easier to just multiply by 24, i.e.
=M7*24
format result cell as number
If you use Chris's  suggested method for fixing the ROW that would then become
=INDIRECT("M"&ROW())*24
although arguably that will cause more problems than just =M7*24, for example the "hardcoded" "M" won't change even if you delete a column, so it might be better to use INDEX, i.e.
=INDEX(M:M,ROW())*24
regards, barry
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.