[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 359
  • Last Modified:

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
0
rfwoolf
Asked:
rfwoolf
  • 5
  • 3
  • 2
  • +1
2 Solutions
 
Chris BottomleyCommented:
Hello rfwoolf,

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

Regards,

chris_bottomley
0
 
Chris BottomleyCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Chris BottomleyCommented:
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 BottomleyCommented:
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 BottomleyCommented:
>>> 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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now