Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula - Not Excel Guru !!!
Hello All,
I require help with the below formula. =(0+(33/60)+(18/(60*60)))*-1 Certain numbers in the formula change. The numbers that change are from left to right '0' '33' & '18'. In this instance the '0' was a negative value (hence the *-1 on the end of the formula). I have many rows in which these numbers vary. How can I target the formula at one cell (where the data source is, eg. -0:33:18) and place the result (in this case -0.555) in another? Sorry if I am not explaining very well. I hope you can understand it though. Thank you for your Help. David. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula - Not Excel Guru !!!
With your time (or 0:33:18) in A2:
=(HOUR(A2)+(MINUTE(A2)/60)+(SECOND(A2)/(60*60)))*-1 HTH, Paul -- "Dave Tee" wrote in message ... Hello All, I require help with the below formula. =(0+(33/60)+(18/(60*60)))*-1 Certain numbers in the formula change. The numbers that change are from left to right '0' '33' & '18'. In this instance the '0' was a negative value (hence the *-1 on the end of the formula). I have many rows in which these numbers vary. How can I target the formula at one cell (where the data source is, eg. -0:33:18) and place the result (in this case -0.555) in another? Sorry if I am not explaining very well. I hope you can understand it though. Thank you for your Help. David. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula - Not Excel Guru !!!
Hello Paul,
I get #VALUE! when I apply the formula. This is to do with gps co-ordinates in time (as you have already noted). I need to take hh.mm.ss format to hh.mmsss - I have got the Latitude calculation resolved; that was quite easy by cell total*24; i.e. 44.55.00*24 = 44.88333; I'm still struggling with Longitude. I am using the following now. =(HOUR(E2)+(MINUTE(E2)/60)+(SECOND(E2)/(60*60)))*-1 Cell E2 has the following in it -0:33:18; I am trying to place the result in Cell I2. Again I get #VALUE! Thoughts?? & Thank you. Dave. (PS - where are you in the world out of curiosity) "PCLIVE" wrote: With your time (or 0:33:18) in A2: =(HOUR(A2)+(MINUTE(A2)/60)+(SECOND(A2)/(60*60)))*-1 HTH, Paul -- "Dave Tee" wrote in message ... Hello All, I require help with the below formula. =(0+(33/60)+(18/(60*60)))*-1 Certain numbers in the formula change. The numbers that change are from left to right '0' '33' & '18'. In this instance the '0' was a negative value (hence the *-1 on the end of the formula). I have many rows in which these numbers vary. How can I target the formula at one cell (where the data source is, eg. -0:33:18) and place the result (in this case -0.555) in another? Sorry if I am not explaining very well. I hope you can understand it though. Thank you for your Help. David. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula - Not Excel Guru !!!
I think your problem is that Excel is treating your -ve time string as a
text string. Try =IF(LEFT(A2)="-",-MID(A2,2,255)*24,A2*24) -- David Biddulph "Dave Tee" wrote in message ... Hello Paul, I get #VALUE! when I apply the formula. This is to do with gps co-ordinates in time (as you have already noted). I need to take hh.mm.ss format to hh.mmsss - I have got the Latitude calculation resolved; that was quite easy by cell total*24; i.e. 44.55.00*24 = 44.88333; I'm still struggling with Longitude. I am using the following now. =(HOUR(E2)+(MINUTE(E2)/60)+(SECOND(E2)/(60*60)))*-1 Cell E2 has the following in it -0:33:18; I am trying to place the result in Cell I2. Again I get #VALUE! Thoughts?? & Thank you. Dave. (PS - where are you in the world out of curiosity) "PCLIVE" wrote: With your time (or 0:33:18) in A2: =(HOUR(A2)+(MINUTE(A2)/60)+(SECOND(A2)/(60*60)))*-1 HTH, Paul -- "Dave Tee" wrote in message ... Hello All, I require help with the below formula. =(0+(33/60)+(18/(60*60)))*-1 Certain numbers in the formula change. The numbers that change are from left to right '0' '33' & '18'. In this instance the '0' was a negative value (hence the *-1 on the end of the formula). I have many rows in which these numbers vary. How can I target the formula at one cell (where the data source is, eg. -0:33:18) and place the result (in this case -0.555) in another? Sorry if I am not explaining very well. I hope you can understand it though. Thank you for your Help. David. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula - Not Excel Guru !!!
Hello David & Paul, Thank you both for your input. The formula seems to work
now. I will come back to you if I encounter any other issues with this. Regards Dave "David Biddulph" wrote: I think your problem is that Excel is treating your -ve time string as a text string. Try =IF(LEFT(A2)="-",-MID(A2,2,255)*24,A2*24) -- David Biddulph "Dave Tee" wrote in message ... Hello Paul, I get #VALUE! when I apply the formula. This is to do with gps co-ordinates in time (as you have already noted). I need to take hh.mm.ss format to hh.mmsss - I have got the Latitude calculation resolved; that was quite easy by cell total*24; i.e. 44.55.00*24 = 44.88333; I'm still struggling with Longitude. I am using the following now. =(HOUR(E2)+(MINUTE(E2)/60)+(SECOND(E2)/(60*60)))*-1 Cell E2 has the following in it -0:33:18; I am trying to place the result in Cell I2. Again I get #VALUE! Thoughts?? & Thank you. Dave. (PS - where are you in the world out of curiosity) "PCLIVE" wrote: With your time (or 0:33:18) in A2: =(HOUR(A2)+(MINUTE(A2)/60)+(SECOND(A2)/(60*60)))*-1 HTH, Paul -- "Dave Tee" wrote in message ... Hello All, I require help with the below formula. =(0+(33/60)+(18/(60*60)))*-1 Certain numbers in the formula change. The numbers that change are from left to right '0' '33' & '18'. In this instance the '0' was a negative value (hence the *-1 on the end of the formula). I have many rows in which these numbers vary. How can I target the formula at one cell (where the data source is, eg. -0:33:18) and place the result (in this case -0.555) in another? Sorry if I am not explaining very well. I hope you can understand it though. Thank you for your Help. David. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL GURU WANTED - Time calculations | Excel Worksheet Functions | |||
I need an excel guru! HELP! | Excel Discussion (Misc queries) | |||
Seeking an Excel guru in the Boston area - EXC102706 | Excel Discussion (Misc queries) | |||
I Need a math guru | Excel Discussion (Misc queries) | |||
Cut and Paste Question for an Excel Guru | Excel Discussion (Misc queries) |