? offset('activecell',,-2,1,1) in Named Formula ?

I have a cell formula as part of a WorkSheet for explaining stats
=prob_T^(A9) * (1-prob_T)^(n-A9)

I would prefer to use a Name, k, in place of the A9 relative
reference, as this would align the WorkSheet with standard stats names
& make explanations easier to understand.

This name needs return a reference to the cell at at columnoffset(-2)
relative to the cell the Name is used in ... the syntax I've used in
the subject line is as close as I've got so far ...

?? is this something that can be made to work?

input gratefully received

Mat
0
Matthew
4/12/2010 1:01:06 PM
excel.programming 6508 articles. 1 followers. Follow

2 Replies
336 Views

Similar Articles

[PageSpeed] 38

You can define k as:
=INDIRECT(RC[-2],FALSE)
or if you only use it on one sheet, then select cell C1 and define k
as:
=A1




Matthew Dodds;696805 Wrote: 
> 
I have a cell formula as part of a WorkSheet for explaining stats
> =prob_T^(A9) * (1-prob_T)^(n-A9)
> 
> I would prefer to use a Name, k, in place of the A9 relative
> reference, as this would align the WorkSheet with standard stats names
> & make explanations easier to understand.
> 
> This name needs return a reference to the cell at at columnoffset(-2)
> relative to the cell the Name is used in ... the syntax I've used in
> the subject line is as close as I've got so far ...
> 
> ?? is this something that can be made to work?
> 
> input gratefully received
> 
> Mat


-- 
aflatoon

Regards,
A.
------------------------------------------------------------------------
aflatoon's Profile: 1501
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=194838

http://www.thecodecage.com/forumz

0
aflatoon
4/12/2010 1:08:35 PM
Hello Matthew,

click on A9
click on the left hand side of the formula bar: fx
type in : k

You can now use k instead of A9.

Best Regards,

Gabor Sebo






"Matthew Dodds" <matthewgdodds@hotmail.com> wrote in message
news:f42385a6-e3bb-4e77-b140-b9b1ea8b8895@x3g2000yqd.googlegroups.com...
>
> I have a cell formula as part of a WorkSheet for explaining stats
> =prob_T^(A9) * (1-prob_T)^(n-A9)
>
> I would prefer to use a Name, k, in place of the A9 relative
> reference, as this would align the WorkSheet with standard stats names
> & make explanations easier to understand.
>
> This name needs return a reference to the cell at at columnoffset(-2)
> relative to the cell the Name is used in ... the syntax I've used in
> the subject line is as close as I've got so far ...
>
> ?? is this something that can be made to work?
>
> input gratefully received
>
> Mat
>

"Matthew Dodds" <matthewgdodds@hotmail.com> wrote in message 
news:f42385a6-e3bb-4e77-b140-b9b1ea8b8895@x3g2000yqd.googlegroups.com...
>
> I have a cell formula as part of a WorkSheet for explaining stats
> =prob_T^(A9) * (1-prob_T)^(n-A9)
>
> I would prefer to use a Name, k, in place of the A9 relative
> reference, as this would align the WorkSheet with standard stats names
> & make explanations easier to understand.
>
> This name needs return a reference to the cell at at columnoffset(-2)
> relative to the cell the Name is used in ... the syntax I've used in
> the subject line is as close as I've got so far ...
>
> ?? is this something that can be made to work?
>
> input gratefully received
>
> Mat
> 

0
helene
4/12/2010 2:04:40 PM
Reply:

Similar Artilces:

Money budget question from a beginner #2
once I create a budget and go to see the budget report, I can see excelent results of the months. The problem is that the report shows only budgeted expenses and if I have total of 1K of unbudgeted amount I will not see that on the report. Does that report and the budged worths anything then Or I'm doing something wrong? I gues it is second choice. Could anybody advise? Thanks. Are you showing income and expenses in your report? what is the name of the report? Is it the one that shows up from the budget left side menu? It should give you a bottom line over/under budget. >-----...

missing Message-ID #2
Outlook 2003 does not by default generate message-ids unless using Exchange server. Is there anyway to enable this? Thanks Message IDs are generated by the server if I am not mistaken. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Dave asked: | Outlook 2003 does not by default generate message-ids | unless using Exchange server. | | Is there anyway to enable thi...

Mathmatical formula help needed
Does anyone know how to write a formula to convert time card punche into payroll friendly numbers? I dont need to figure out how man hours an employee has worked, that is already done. I just need t convert the time worked to a number usable by payroll. I will b dealing with numbers like 27.48 that need to be rounded to the neares thenth of an hour. I have found lots of charts that show what to roun to at what times, but I dont want to use Vlookup for this. Is there simple formula for this? Thanks, Kingtrito -- Message posted from http://www.ExcelForum.com Hi Kingtriton, Use the ROUN...

Duplicate Records #2
I am not real proficient in Excel but received a large (18,000+) file from Data Processing in Excel to use for a mailing. My question is, I need to find duplicate addresses and delete those before I do the mailing. I work for the school district, so many families have more than one child in the district and they only need to receive one letter. Is there a way to do this? Thanks, Kristy -- Kristy ------------------------------------------------------------------------ Kristy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37546 View this thread: http://w...

Question about Service Pack 2
We just updated to SP2 on our Exchange box. We used to be able to go to OWA and use our username to log in, now we have to type our entire email address to get access. Any way to change that back to just the username? Thanks! Douglas Are you using FBA? Did you modify the owa logon.asp page as per: http://www.msexchange.org/tutorials/OWA2003Forms-based-Authentication-default-domain.html Nue "Douglas McIver" <dmciver@NOSPAM.jlmdirect.NOSPAM.com> wrote in message news:OHA1TDIIGHA.2040@TK2MSFTNGP14.phx.gbl... > We just updated to SP2 on our Exchange box. We used to...

vlookup and offset
Hi, Can anyone PLEASE help me with the following? - it's driving me cRaZy!! I want to look up a value in column B and return the contents of the cell across 4 and down 4 from the value looked up in column B. Is this possible? Rachel ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ "RachelS" <RachelS.vyc4y@excelforum-nospam.com> wrote in message news:RachelS.vyc4y@excelforum-nospam.com... > Hi, > > Can anyone PLEASE help me with the foll...

formula finding price based on volume bracket
I want to write a formula, say in cell E2 that picks the correct price based on valume Entered in Cell D1. Suppose I have a table set up with volume vs price. sush as: Column A B C D E Row 1 From TO PRICE Volume Entered ?? Row 2 500 600 $1.20 Row 3 601 700 $0.99 Etc... down the row. I know I can use vlookup ordinarily but now there are two columns. If volumn is between 500 to 600 then the price is one thing, if volumn is between a different braket is something else.....

running check link #2
Does anyone have a Information about the Check Links Analytical Accounting process in Microsoft Dynamics GP 10. What in particular are you looking for? Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblosgter.blogspot.com "Anav" wrote: > Does anyone have a Information about the Check Links Analytical Accounting > process in Microsoft Dynamics GP 10. > > > I have a client who ran the routine of links analytical accounting, after this genre hav...

how to get the lenth and offset of paragraph?
To All: I have a pragram ,here only is a example similar to My pragram �� these are some data : AAAA this is a test! here is the content of AAAA. BBBB this is a test,I want to know that between AAAA and BBBB lenth . CCCC this is a test! here how can I know CCCC excursion is what relative to AAAA�� best regards! Terrcy Application : SDI View : Derived from CEditView. Menu item added : Calculate Length Paste paragraph mentioned in your mail in view.. be sure to remove any leading blank spaces before 4 BBBB line... Imp...

2 Entities and 1 Workflow Rule
Hi there! I have seen in earlier post that we can alter a value of an Account's field through a workflow rule of a Case concerning that Account. I cannot understand how I can do this, influence between two entities using a Workflow rule. Also, I don't understand the use of Post Url Action, athough I read about it... Can you help me and give directions or examples? Thank you Elena When you add the "update object" action, it will give you a pick list of objects you can update from within that object (the object you are writing the rule for). For example, when you ...

Formula? #2
Could someone to help me to write a formula which could do such thing: "mother" to change to "rehtom" Im not a beginer at using excel but can not think about righ formula. Than -- Message posted from http://www.ExcelForum.com In a standard module paste: Function ReverseString(rng As Range) As String Dim i As Integer For i = Len(rng.Value) To 1 Step -1 ReverseString = ReverseString & Mid(rng.Value, i, 1) Next End Function in A1 -->> Mother Formula in B1 =ReverseString(A1) --- Displays --->> rehtoM HTH "Giedriu...

Checkbox question #2
can you "lock" a checkbox to its linked cell in regards to height, width, and position? In other words once placed, the checkbox would cover the cell, and if the size or position of the cell is changed the size/position of the box would alter with it. if so: what is the setting? how would this need to be noted in VBA? For Each rCell In rng With SH.CheckBoxes.Add(rCell.Left + 4, _ rCell.Top - 2, 0, 0) .Caption = "" .LinkedCell = rCell.Address(0, 0) End With ...

How can I can add "graphite light att" font 2 my home xp?
I transferred a Publisher file from one computer (ME) to a new computer (XP home ed). Both computers use MS office 2000. When I re-open that file now (on the XP computer), a box pops up indicating the font type is unknown and the status as being unavailable. Therfore the embedded font - "graphite light att" is modified to another font. I want too keep the original font! How do I get this font uploaded to my XP computer so the font on my document remains? Copy the font from your old computer on a floppy or whatever, install it on your new computer. -- Mary Sauer MSFT MVP http...

RoboToolz RT-7715-2 Automatic Leveling 2-Beam Level and Plumb Crossliner
Price:$299.00 Image: http://thediscounfinder.info/image.php?id=B000CPJ3UQ Best deal: http://thediscounfinder.info/index.php?id=B000CPJ3UQ I AM VDERY PLEASED WITH THE LEVEL, SEEMS WELL MADE WITH NICE CASE. THE SIZE IS ALSO NICE AND EASE OF OPERATION. I DEFINITELY LIKE HAVING THE CROSSLINE FEATURE, HANDY FOR INSTALLING CABINETS. this thing has helped us layout and reduce our errors for measuring showers & Mirrors. not that great in real bright light and long distances from the source, but an excellent value. This level works great for simple indoor tasks. The mounting options are gre...

Vlookup and offset
Hi, I would like to lookup a users ID in a table and check if they have a paticular qualification, a normal VLOOKUP will do that but then I would like to refer to a date in the header to see when they gained the qualification so the cell can determine whether they are qualified based on the current date. I could do this without checking the date but that would disguise the lack of skills for the rest of the past records as soon as the skill was recorded. I think I need to combine an IF, VLOOKUP and an OFFSET but am at a loss as to how to put it all together. Hope someone can help. ...

Unsafe Attachments #2
How do you stop OE from removing so called unsafe attachments BOb Robert Brown wrote: > How do you stop OE from removing so called unsafe attachments BOb have you never heard of Google? this must be the MOST ASKED question in HISTORY. Hit forward, then open or save the attachment and close the message. It's a new security feature to prevent accidental opening of viruses, either by yourself or scripting. you can disable it in tools | options | security, but I think the extra effort to hit forward is safer and well worth the extra click. For more information, see http://www.om...

Excel Won't open #2
Suddenly getting "wait while configure Office XP standard" when opening Excel. Then it wants the disk. Excel was purchased online/download, so no disk. Updates have not fixed problem. Some error messages are "stdret.msi" and "Error 1706." Al Unless this is a trial version then Excel cannot be legitimately downloaded, as far as I know. (At least without writing it to a CD) and the download would also be immense. If it is a trial version then MS support these so you can ring your subsidiary. If it came as pre-loaded on a machine then the manufacturer of the ...

more than 255 lines in chart #2
Is there a work-around the 255-line limit in Excel ?? Transpose won't do the trick, as I need 22 columns of data. Excel 2000 SP3, on Win XP Pro SP2. Michael Hi Michael - That's a lot of series for a chart. Is it going to be legible with so many lines? I've only ever seen one chart that really needed so many series; an experimental meteorologist needed custom curves on a chart (non-cartesian lines, like isobars). I was able to help him combine around a thousand of these curves into a few dozen. If you describe why you need all the series, someone might think of a similar wa...

VLOOKUP Nesting Formulas #3
Thank you, this is exactly what I needed! : -- ajpower ----------------------------------------------------------------------- ajpowers's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=940 View this thread: http://www.excelforum.com/showthread.php?threadid=27096 ...

TESTING #2
(SORRY TESTIN (SORRY -------- Message sent from www.excelforums.co The ORIGINAL excel forums, now pushing all posts out to USENET. Don't apologize, learn about the microsoft.public.test.here newsgroup! In article <OYxvgTCjEHA.3876@TK2MSFTNGP15.phx.gbl>, balazs@saltspringmedia-dot-com.no-spam.invalid (Balazs) wrote: > (SORRY) > TESTING > (SORRY) Thanks, I didn't realized they eliminated 3 and added a new one actually think this name makes a bit more sense, and removes some of the abiguity about what it is for. news://msnews.microsoft.com/microsoft.public.t...

outlook and exchange #2
Hi, I am running winxp pro and outlook2002 connecting to exchange2000. One machine will not let me open the mail folders and says the user doesn't have permission. however, different users on the same system have no problems and the logon credentials work on other PCs. App log also shows no errors. Any ideas? Thanks folks are u using the MAPI account ? "mark" <anonymous@discussions.microsoft.com> wrote in message news:0c2d01c3af55$3f2312d0$a301280a@phx.gbl... > Hi, > > I am running winxp pro and outlook2002 connecting to > exchange2000. One machine...

Check Recipient Names in Outlook 2007
I just imported a Contact List in Outlook 2007. Is there a way to check the email addresses for errors before sending a email to the contacts? My problem is that when I send out the emails, some emails are not correct and sening the email fails. Can anyone help me? -- RES Outlook will tell when it encounters any email address that is not valid. If the email address is incorrect, how could Outlook know that? Exactly what "errors" did your import produce? What file type did you import? -- Russ Valentine "RES" <RES@discussions.microsoft.com> wr...

Index Function #2
I have posted the file DB Practice.xls, at https://mediafire.com, I beleive that this is a file sharing site unlike the previous site. I think the questions contained in the file explain the problem I am having with the index function. I am trying to retreive the intersecting cell value using the Index Function. I can enter row and column arguments manually and it works, but when I point to cells on other sheets containing the same values for the arguments the function returns a ERROR. I wouls appreciate help. Thanks I think we need a link to the specific file - at which ever site you...

Outlook has caused an error in MSVCRT.DLL #2
Outlook has caused an error in MSVCRT.DLL (this is the exact message before Outlook collapses) I use Windows ME and I tried replacing the DLL file and re- installing Outlook. Anyone with a solution? Thanks which version of Outlook? try installing the latest Service Pack for the version of Office/Outlook installed. "peter moree" <peter@moree.nl> wrote in message news:1b3801c49a38$b969c7e0$a301280a@phx.gbl... > Outlook has caused an error in MSVCRT.DLL (this is the > exact message before Outlook collapses) > > I use Windows ME and I tried replacing the DLL fil...

offset
need soem help. user types in a value in a1 in b2 i want to display the results in a3:a50 i have the data i want to display in b2 corresponding to the number entered in a1 how is this done? so if 13 is entered in a1 then i want to display want b2 to equal what is in cell a13 Hi Pete, If I understand you correctly, try, =OFFSET($A$2,$A$1-2,0) Hope this helps! In article <Xns950D8EB788ACA123abcdude@207.115.63.158>, Pete <anonymous@discussions.microsoft.com> wrote: > need soem help. > user types in a value in a1 > > in b2 i want to display the results > &...