Complex excel - calling formulae result and setting as value of a specific cell

To cut a long story short, I need to convert mapping coordinates in a
specific projection (the Australian Map Grid, or AMG) to a Lat/Long in
degrees/minutes/seconds using Redfearn's Formula.

There is already an excel worksheet out there which converts these on
a one-by-one basis, which is available from:
http://www.ga.gov.au/geodesy/datums/redfearn.xls

What I'd like to do is set the value of a cell based on the formula
output, eg:

=redfearn_lat(X,Y,Z)
=redfearn_long(X,Y,Z)

With X being the cell reference for the easting value, Y for the
northing value, and Z being the Zone.

Any help appreciated.


0
11/9/2009 3:09:25 AM
excel 39879 articles. 2 followers. Follow

1 Replies
483 Views

Similar Articles

[PageSpeed] 32

Hi Michael,

If you unhide the hidden rows in that worksheet, you'll see there are numerous constants and intermediate formulae used to do the 
transformation, some of which refer back to the 'Constants & Parameters' worksheet. Thus, what you've got is not so much a formula 
as a set of lookup tables. Unless you've got access to the underlying formula, all these factors would need to be coded into your 
UDF.

-- 
Cheers
macropod
[Microsoft MVP - Word]


"Michael Harris" <michael.harris@fpc.wa.gov.au> wrote in message 
news:d5d8a988-8a89-4595-abcf-0d540bbf7cf8@f20g2000prn.googlegroups.com...
> To cut a long story short, I need to convert mapping coordinates in a
> specific projection (the Australian Map Grid, or AMG) to a Lat/Long in
> degrees/minutes/seconds using Redfearn's Formula.
>
> There is already an excel worksheet out there which converts these on
> a one-by-one basis, which is available from:
> http://www.ga.gov.au/geodesy/datums/redfearn.xls
>
> What I'd like to do is set the value of a cell based on the formula
> output, eg:
>
> =redfearn_lat(X,Y,Z)
> =redfearn_long(X,Y,Z)
>
> With X being the cell reference for the easting value, Y for the
> northing value, and Z being the Zone.
>
> Any help appreciated.
>
> 

0
macropod1 (83)
11/9/2009 10:18:32 PM
Reply:

Similar Artilces:

Changing Text to Time in Excel
I have a large database that is sent to me for anaysis on a regular basis. My problem is with times. They are sent to me in the for 0122 as text. I need help converting them to a time that excel can deal with (without manually fixing them, as there's about 100,000 pieces of data that need to be updated). Thanks your your help. add a helper column =TIME(LEFT(A2,2),Right(A2,2),0) and copy down -- HTH Bob Phillips "Chip193" <Chip193@discussions.microsoft.com> wrote in message news:D4E15A55-9B11-41D4-AF92-CBE2B3AC561A@microsoft.com... > I have a large databa...

SUM cells with values within intervalls?
Hi, What is the best approach - or maybe you have a solution? I need to check a column with values and sum the cells with values within intervals, like 0-100000, 100001-500000 and 500001-1000000 There is a column with values within these intervals and as result I want to have three rows with sums of these intervals. I tried pivottables, but it seems not to be able to create intevals in it I have tried to create array formulas that sum the matrix values if interval is true, but bite the dust. Can't find a formula that return a matrix/areas with cells within the intervalls(to use wi...

Default Value for bits
Hi, I would like to know where is the default value of bit fields are kept in the database. I found in Metabase, in table attribute, a column DefaultValue but this column is not working, if I change in CRM the predefined value of a bit field nothing happens to the defaultvalue in Metabase it's always with NULL value. This is strange because if I change in CRM the bit value it saves my choice. But WHERE?? There must be another place in the database that I can't find... Thanks It's stored with the form definition, which you can find in the FieldXML (or possibly FormXML) field...

Running totals in excel
Good afternoon all. I need a little assistance with using spreadsheets to keep an inventory updated. I have 7 column headings each referring to a different type of equipment. I have another column for a date, one representing whether the the equipment was shipped or received and finally which facility was receiving or shipping the product. So in each row I would enter a date, how many pieces of equipment was either shipped or received and then the shipping / receiving facility. My questions are, how would I keep a running total of a particular facilities current inventory with so many...

Excel sheet into another application for printing question
I have a document which is in fact a price list of publications which I sell from various countries around the world. I have composed it in Excel because I like the ability to manipulate the prices at a stroke using the calculation functions within Excel. The problem is that the document does not look very pretty and is not easy to format within Excel. It consists of five columns and about 300 rows. By selecting groups of the cells I can copy and paste them so that it becomes a more manageable document namely it becomes 10 columns and about 150 rows. I'm just wondering if there is a b...

Public Folder tree
We have some public folder trees that are getting 5-10 levels deep, as they are by default matching our projects drive folder structure. I am just wondering, is there a limit to how deep and wide a public folder matrix can/should go? I can easily see a structure of 20 projects, each as many as a hundred subfolders in a tree up to 10 levels deep, with literally thousands of files in each project. Am I courting disaster? Thanks, Gordon On Tue, 1 Nov 2005 11:02:09 -0800, Gordon Price <GordonPrice@discussions.microsoft.com> wrote: >We have some public folder trees that are getting...

Complex formula change making m,e crazy
I have thsi formula... myFormula = "=IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,""/""," _ & "REPT("" "",100)),100))),""UNKNOWN""," _ & "IF(--TRIM(RIGHT(SUBSTITUTE(H2,""/""," _ & "REPT("" "",100)),100))<999," _ & "VALUE(TRIM(RIGHT(SUBSTITUTE(H2,""/""," _ & "REPT("" "",100)),100))),""UNKNOWN"...

maintaining cell formating
I select a column and format it for dates (3/4/08). All dates I type are in that format. I save the file and close it. When I reopen it the old data is in the correct format but any new date defaults to (3-March). Why won't it maintain the cell format? Or better yet, how do I get it to maintain the saved format? In article <ee8c3cc.-1@webcrossing.caR9absDaxw>, taichimac@officeformac.com wrote: > I select a column and format it for dates (3/4/08). All dates I type are in > that format. I save the file and close it. When I reopen it the old data is > in the correct format...

Is there a PeeWee Football Play template for Visio? Excel?
...

Excel & Word with automatic numbering
How do I set-up a 3 line label in such a way that the 3rd line will number in sequences of 1 automatically? One way: Insert a SEQ field in your label. In article <10008FA0-7A51-4398-AC82-5C9D88414E36@microsoft.com>, "Heather" <Heather@discussions.microsoft.com> wrote: > How do I set-up a 3 line label in such a way that the 3rd line will number in > sequences of 1 automatically? ...

How do I find missing value?
At any time I would need to enter the three values I have to solve for the fourth. When I have Margin, Index and rate I will need teaser. When I have Teaser, Index, and Rate I will need Margin. Teaser=(INDEX + Margin)-Start rate' Margin=(Rate + Teaser)-INDEX' Index=(Rate + Teaser) - Margin Rate = Will not need to solve for this! What is your question? Are you saying that you have 4 columns labeled Teaser, Index, Margin, and Rate, and you want to solve for the fourth one given the other 3? Please post back and clarify what you want/need. HTH Otto "edwinvo...

Printing Complex Workbooks
Hi -- I am wondering if anyone has experienced this issue. Some of my users have created very complex workbooks, with different page orientations, formulas, colors and different page sizes. When these documents are printed (NDPS), Excel crashes. This only happens when the entire workbook is printed. Not the active page. The only solution is to use an older version of the Novell print driver - NDPPNT.DLL. Environment specs: Windows 2000 Excel 2000 Netware 5.1 SP6 Novell Client 4.90 HP PCL 5E (latest firmware on printers, latest version of PCL 5E driver) Novell's stance is if pr...

Detecting Excel 97 using VBA
Hi. I need to detect, in a VBA script, if it is executed by Excel 97. How do I do that? Casper Casper; MsgBox "Welcome to Microsoft Excel version " & _ Application.Version & " running on " & _ Application.OperatingSystem & "!"Shows the version number in a messagebox Mark.-- Meer Excel ? www.rosenkrantz.nl of contact@rosenkrantz.nl------------------------------------------------------ -------------Rosenkrantz Spreadsheet SolutionsWitkopeend 241423 SN UithoornNederlandTel : 0297-527511--------------------------------------------------...

Setting up account for outlook
I am trying to set up my outlook on my computer using Windows Mail. I get to the part where it asks me about my provider/server information and I am lost. I do not know what kind of server my incoming or outgoing mail is nor do I know how to find it. I get this error: The host 'HTML' could not be found. Please verify that you have entered the server name correctly. Subject 'test', Account: 'SMTP', Server: 'HTML', Protocol: SMTP, Port: 25, Secure(SSL): No, Socket Error: 11001, Error Number: 0x800CCC0D -- Anthony Outlook or Outlook Express...

Excel 2003 and 2007
How can I run both versions on the same PC at the same time Paul "PA" <PA@discussions.microsoft.com> wrote in message news:18191163-55CD-44C8-8CE4-C401A9BB380C@microsoft.com... > How can I run both versions on the same PC at the same time > > Paul Google got this first hit....... http://support.microsoft.com/kb/218861 Thanks Gordon, but unless I missed something this article doesnot address version 2007. "Gordon" wrote: > "PA" <PA@discussions.microsoft.com> wrote in message > news:18191163-55CD-44C8-8CE4-C401A9BB380C@microsof...

FRx cell formatting
It would be a nice feature to have specific cell formatting in FRx. For example having the % format on several rows and then within the same report changing that to number formatting. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businessso...

how do I set "all day event" as default
creating an appointment in Calendar , how do I set "all day event" checked as the default? Appointments are timed, all day events are not. Create only All Day events is your answer. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft.com/KB/555375 After furious head scratching, Old Standard asked: | creating an appointment in Calendar , how do I set "all day event" | checked as the default? Very good. Now is there a pl...

Reset option buttons to default value
I'm setting up a questionnaire on a worksheet and have assigned pairs of option buttons as Yes / No to compile answers to a series of questions. I have 10 pairs of option button on one sheet, each with their own unique name. The option buttons are Active X. However only one option button at any time can be checked. Why is that and what can I do to prevent it? I want one button of each pair to be checked as an individual works through the questions. Second related question: At the end of the questionnaire I want to reset all the option buttons to their default value, which for the Yes...

Excel quits responding searching for files not in my documents
Office 2003 When I use CTRL O to open a document that is not in My Documents, the program all but quits responding. This is happening in Excel and Word. If I am willing to wait it will eventually give me the opportunity to search other directories. Opening a document using Windows Explorer gives me an immediate response. ...

Please help how to Compare tables in Excel
I would like to know how is the easiest way to take two tables in Excel and compare against each other and find for instance if there are no matches? Any help will be greatly appreciated. You can compare two worksheets using VBA if you have a lot of potential differences using this: http://www.exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html If you want a way to do it with formulas, please give more info on what's in the worksheets. "TotallyConfused" <TotallyConfused@discussions.microsoft.com> wrote in message news:1FB05585-5BA4-4015-85...

query is too complex error message
does anyone know if access 2007 has increased the capabilities of their queries? i have a query that was built in the 2003 version that keeps giving me the subject line error message. the query includes 7 fields that have a criteria requirements. it works for about a month and then just stops working. any help would be appreciated. thanks,andrea-- Andrea On Tue, 27 Mar 2007 18:13:51 -0700, Andrea <Andrea@discussions.microsoft.com>wrote:>the query criteria is as follows:>>([forms]![frmSearchByMultipleFunctions]![ThisValue5] >or([forms]![frmSearchByMultipleFunctions]![ThisVa...

How do I set up an outlook e-mail account
There is no send button on my outlook express pop-up for e-mails. How do I set up an account so I can actually use this? To add a POP3/IMAP account in Outlook Express, just pick Tools > Accounts > Add button in the main Outlook Express window. "Beachnut62" <Beachnut62@discussions.microsoft.com> wrote in message news:C9618D78-981C-4D9B-85FB-24B630F0EE86@microsoft.com... > There is no send button on my outlook express pop-up for e-mails. How do > I > set up an account so I can actually use this? I have a similar problem with getting Outlook 20...

How to remember shortcuts in excel?
Shortcuts keys in Excel are always assigned based on the first letter of the menu item. For example. to open a new file the shortcut is CTRL + N, N is the first letter of the new command. For more details, see this post http://xlmaster.blogspot.com Agreed for New, Open, Save, Print but don't forget Control + C is Copy and Paste is Ctrl + V Cut is C... I shan't go on, there aren't any general rules Steve On Tue, 08 Aug 2006 10:11:42 +0100, Mr.Cools <prabhuraaman@gmail.com> wrote: > Shortcuts keys in Excel are always assigned based on the first letter > of the ...

Export excel sheet to outlook
I have an Excel spreadsheet containing several addresses, I need to export to a "Contact" folder in Outlook; my problem is that, after choosing the destination file, I get the following error message: The Microsoft Excel 97-2003 file X:\xxx\xxxxxx has no named ranges. Use Excel 97-2003 to name ther range of data you want to import I tried to use the Help in Exce but I did not find any clear, step by step explanation about how to do it. Thanks in advance for your help. Stefano Stefano Highlight the data in you Excel workbook (Including headers) and then go to Insert>Name...

From Excel to VBA Chart appearance
Does anyone know when you move an application out of excel to a vba platform, is there any loss in quality and capabilities in the charts? Kind regards, Mark Mark Both of your questions have gone unanswered and I suspect it is a confusion. To get the data to any type of chart object/applet you would need to automate XL. While you are doing that, why would you want to *not* use XL's charting features, via the same automation? Is there something missing in XL's features. If you are using automation, then by default, you are using XL's charting object and there will be ...