Setting a dynamic range in a formula

Hi,
I have a column of numbers and I always want the following arra
formula to use the last 12 entries:
=(PRODUCT(1+D1:D12/100)-1)*100
Any suggestions?
Thanks,
Phillycheese

--
Phillycheese
-----------------------------------------------------------------------
Phillycheese5's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2419
View this thread: http://www.excelforum.com/showthread.php?threadid=37809

0
6/10/2005 3:17:19 PM
excel 39879 articles. 2 followers. Follow

3 Replies
468 Views

Similar Articles

[PageSpeed] 2

Assuming that Column D contains no blanks, try...

=(PRODUCT(1+OFFSET(D1,MAX(0,COUNTA(D:D)-12),0,12,1)/100)-1)*100

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Phillycheese5 Wrote: 
> Hi,
> I have a column of numbers and I always want the following array
> formula to use the last 12 entries:
> =(PRODUCT(1+D1:D12/100)-1)*100
> Any suggestions?
> Thanks,
> Phillycheese5


-- 
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10785
View this thread: http://www.excelforum.com/showthread.php?threadid=378090

0
6/10/2005 4:49:31 PM
One way:

   =(PRODUCT(1+OFFSET($D$1,COUNTA(D:D)-12,0,12,1)/100)-1)*100

In article 
<Phillycheese5.1qf4ea_1118419501.7422@excelforum-nospam.com>,
 Phillycheese5 
 <Phillycheese5.1qf4ea_1118419501.7422@excelforum-nospam.com> wrote:

> Hi,
> I have a column of numbers and I always want the following array
> formula to use the last 12 entries:
> =(PRODUCT(1+D1:D12/100)-1)*100
> Any suggestions?
> Thanks,
> Phillycheese5
0
jemcgimpsey (6723)
6/10/2005 4:52:00 PM
Correction...

Formula to be confirmed with just ENTER, not CONTROL+SHIFT+ENTER.

In article <Domenic.1qf76a_1118423102.1155@excelforum-nospam.com>,
 Domenic <Domenic.1qf76a_1118423102.1155@excelforum-nospam.com> wrote:

> Assuming that Column D contains no blanks, try...
> 
> =(PRODUCT(1+OFFSET(D1,MAX(0,COUNTA(D:D)-12),0,12,1)/100)-1)*100
> 
> ..confirmed with CONTROL+SHIFT+ENTER.
> 
> Hope this helps!
> 
> Phillycheese5 Wrote: 
> > Hi,
> > I have a column of numbers and I always want the following array
> > formula to use the last 12 entries:
> > =(PRODUCT(1+D1:D12/100)-1)*100
> > Any suggestions?
> > Thanks,
> > Phillycheese5
0
domenic22 (716)
6/10/2005 5:12:55 PM
Reply:

Similar Artilces:

cannot find "custom property sets"
Running Visio Pro 2002 SP2, I am trying to run the following instruction: "If you have Visio 2002, try these: Tools > Macros > Visio Extras > Apply Custom Property Set Tools > Macros > Visio Extras > Edit Custom Property Sets" Once the macro panel pops up there is no selection or option for VISIO EXTRAS. Do I have to enable it? I find nothing in the "help" about enabling extras... Thanks I thought they were new to v2003.... besides on my system, I get to them via the tools => custom property sets al <rhatcher@titan.com> wrote in message ne...

Difficulty in setting hotmail acct. as main (personal) folder
Just recently I purchased a new Pocket PC handheld which came wit inbox(ppc) and outlook 2002(pc). I installed them both and they wor great. I can sync my emails to the unit and read them anywhere. However, I noticed that it will only import items from my persona folder (which seems is a non-html email acct). Question: Is it possible to configure outlook 2002 to make hotmail th personal folder? If not, would it be possible to create a macro tha can transfer all emails from the hotmail acct to the personal folder? It would be a time saver. Thanks, Hovi ----------------------------------...

setting font size in dos
I am creating a text file which has to be send for printing in DOS format. How do i set the font size. please help and guide me. Best regards Vivek Vivek, To control font sizes printing from DOS, you'll have to send control codes to the printer during print-out (normally a series of unprintable characters). Those are specific for different brands, and you'll have to consult the manual for you printer. To avoid this hassle was one of the main benefits of Windows for us poor programmers :-))) Note also that when you print normal text, the printer will treat it as such, (and not as gr...

Generic row reference in formula
I am using a formula in Excel to sum the values in a row: =SUM(B2:F2) Is there a way to use a generic row reference in a formula? For example, I don't know for sure at run time if the data and formula is going to end up in the second row. What I want is something like this =SUM(B#:F#) where the # sign would indicate the current row containing the formula. Thank you very much. Rick Quatro rickquatro@gmail.com One way: =SUM(INDIRECT("B"&ROW()):INDIRECT("F"&ROW())) -- HTH, RD --------------------------------------------------------------------------- P...

VB- If first cell with formula is blank, all cells in column returns blank.
Hi all, I am using ADO to connect to an excel sheet and display the data in vb form. In excel there is a column named "TAT" which has a formula t add two othe cell values to it. If any of the two cells is blank the the TAT col remains blank. If the very first cell in the TAT column has some value then VB showa all cell values in that column. But if the first cell is empty then al cells in tat column are shown blank, even if there are values in othe cells. What's the solution for this? Thank -- Message posted from http://www.ExcelForum.com Hi Of course you can try with some...

Mail Merge using Named Range in Excel
Having trouble completing a mail merge in Word using an Excel spreadsheet. In the spreadsheet there are named ranges, I have included the column headings by defining the range that is non-contiguous (not next to each other). When I go to complete the merge, the range will not show up as an option to use. If I re-define the range to not include the column headings and the range is a block of cells next to each other, the merge still doesn't work correctly, it assumes the merge assumes the row above the range is the column heading row (i.e. range is A16:J101, it uses row 17 as the column ...

This message uses a character set.....?
Some users have been complaining about this e-mail. I have no idea what it means but every once in a while, someone in the office will get this. This message uses a character set that is not supported by the Internet Service. To view the original message content, open the attached message. If the text doesn't display correctly, save the attachment to disk, and then open it using a viewer that can display the original character set. Any help is appreciated. It means exactly what it says. You dont have the correct character sets installed to view the message ( it could be in Chinese ...

Dynamics GP KB site not working
Dear All, I have been trying to access KB for 10 hours, but every time I clicked on any searched article, I got an error message saying "Our Applogise.... An error has occurred while retrieving this document." Is anyone else also experiencing the same problem with KB. Thanks, - Amjad Ali Yes. Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com get your gptip42today at www.gp2themax.blogspot.com Knowledge Base site is up and running again. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC ht...

Formula name
Hello, For the life of me, I can't figure out how to copy/paste a formula into the Name>Define>Refers To box. I've used named formulas many times but just typed the formula in. This formula is very long and complex and I just don't feel like typing it into the Refers To box. Can someone 'guide' me through this please? It's got to be simple but I'm overlooking something. TIA Sandy Sandy, copy the formula and use Ctrl v to put it in the refers to box -- Paul B Always backup your data before trying something new Using Excel 2000 & 97 Please post a...

How to set program to view hyperlink?
When I make a hyperlink in Excel 2000, I cannot change which program opens it. Where is the set file associations setting? I am hyperlinking to jpgs. It opens my paint program (PSP) instead of my clipart viewer (ACDSee). I have disassociated PSP with jpg - jpeg. Associated ACDSee with jpg -jpeg. Set Windows Explorer\Tools\Folder Options\File Types to open jpg - jpeg extensions with ACDSee. This was working, now all a sudden it has changed. Any ideas? Thanks, Carol Hi any chance your Windows settings have been changed?. >-----Original Message----- >When I make a hyperlink in Excel 2...

Unable to set security on public folders
I am having trouble setting security on public folders on Exchange 2000 SP3 server. Through System Manager I go into the properties of a public folder, security, client permissions, add user, and the GAL does not display any entries. My Outlook clients are able to view the GAL without a problem. Has anyone run across this problem before and have an idea how to resolve it? Sincerely, Kelly ...

how do i set up email accounts such as yahoo, gmail, aol...?
how do i set up my outlook so i can recieve all my emails from all of my acounts, yahoo, gmail, hotmail, aol, and a school email? jake wrote: > how do i set up my outlook so i can recieve all my emails from all of my > acounts, yahoo, gmail, hotmail, aol, and a school email? Follow the instructions provided by the e-mail provider regarding the server names, ports, account info, etc. and where do i find those? i have searched the sites, such as gone on to yahoo and google and could not retrieve th information "VanguardLH" wrote: > jake wrote: > > > how do i...

How do I get charts to ignore results of a formula if it equals 0
Hi, Use NA() instead of zero. Depending on your chart type the point will not be plotted. Cheers Andy ChrisRox wrote: -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Last cell in new formula
Can anyone please help? The code: Sub test() Dim x As Range Worksheets("sheet1").Activate Set x = Cells(Rows.Count, "N").End(xlUp) MsgBox x.Address End Sub I would like to find the last cell in column N and instead of the result being displayed in A msgbox, I would like to use the cell address in a formula after the code above e.g.: range("b2").formula = _ "sumproduct((N8:LASTCELLADDRESS>=0)*(N8:LASTCELLADDRESS<=1000)) Hi, Try this Dim LASTCELLADDRESS As Long LASTCELLADDRESS = Cells(Cells.Rows.Count, "...

Setting up the Viewing Area
How do I set up the main viewing panes so that I show Mail but where the reading pane is, I show a calendar and a tasks pane instead. The online help talks about docking and undocking panes but the handle isn't visible to grab. Use the folders view and select to view the calendar. --� 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, Geoff Schaller asked: | How do I...

transferring settings for Outlook 2003 from old harddrive to new h
I just bought a new computor. I installed the harddrive from my old computor into the new one. I have tried to use the files and settings transfer wizard to transfer the settings that I had for Outlook 2003 on my old harddrive to the new one. But it is not working. Can anyone suggest how I'd do this manually without the wizard? Been without my mail for too long. -- evan Here's a really good way to restore: http://www.howto-outlook.com/howto/backupandrestore.htm -- Kathleen Orland Blessed are the cracked, for it is they who let in the light Outlook Tips: http://www.outlook-ti...

how do I set up publisher 2000 to print business cards 10 per pag.
I can only get 4 on a page. When you are ready to print, in the print dialog, you will be able to change the page properties. Click page options, custom options, change the side margin to .75, change the top margin to .5, change the gaps to zero. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "yvette" <yvette@discussions.microsoft.com> wrote in message news:6D4E614E-3F33-456D-B7D9-E1A8CD452C13@microsoft.com... >I can only get 4 on a page. Here's I do it with Publisher 2000. File Page Setup Special Si...

formula will not add up right
I put in =sum(E1+G23) & the total in the cell where I put the formula comes up(2 or 3) cents off? It comes up short when I put =sum(E1*G23). Not al the time but off & on. I have to add stuff on paper to check that the total is right. I should not have to take all this time to use Excel. Your question has already been answered an hour and a half ago -- Kind regards, Niek Otten "Kickstart" <Kickstart@discussions.microsoft.com> wrote in message news:DDD6AB31-657B-4F64-8709-D9E36CF96C41@microsoft.com... >I put in =sum(E1+G23) & the total in the cell where I p...

Resize dynamically the size of the CFormView
Hello, I have a view which derives directly from the CFormView and then I display a child dialog on it at runtime. Visual Studio creates automatically resource entry for my view class. The dialog can be resied dynamically by the user by pressing the button and I would like the view to always fit the size of the dialog. At the moment when I press a button and the dialog dimensions change I cannot resize the view and a scrollbar is created, which looks really ugly. Could someone help me with this problem? How can I automatically resize the view so that it always fits the dialog window? Every...

Lookup last in column formulas
Hello Excel users and experts, Is there any significant difference in these formulas that return the last value in the column. =LOOKUP(9.99999999999999+307,A:A) =LOOKUP(9.99999999999999E+307,A:A) =LOOKUP(9.999+307,A:A) =LOOKUP(9.999E+307,A:A) Why use fourteen 9's past the decimal point, either with or without the E when three 9's with or without the E works just as well? Thanks Howard Hi! Good question. Here's a bone of contention I have when I see these types of formulas. Say for example that you know for certain that the absolute largest number that could possibly ...

setting up the browser?
Can anyone assist in how I would go about setting up the browser to be able to preview the web page before publishing it? thank you...p.s. the error message I get is "Y ou do not currently have a browser program registered to show HTML(HTM) files." CK1 wrote: > Can anyone assist in how I would go about setting up the browser to > be able to preview the web page before publishing it? thank > you...p.s. the error message I get is "Y ou do not currently have a > browser program registered to show HTML(HTM) files." Wrong group. Please repost to the webdesign group...

User settings and config files
This is not exactly a C# question by my app is in C# and I'm new to .Net. In my development and testing cycle I frequently must delete folders similar to "C:\Documents and Settings\user\Local Settings\Application Data\company\program.vshost.exe*". I'm afraid this may become a problem after deployment too. How do I deal with these user settings files? One version of my app is deployed with only the default settings. The same version may be deployed a second or third time on the same PC but with program.exe.config files containing settings that override the defau...

Customised power & sounds settings defaulting
Win XP Home SP3 (Single user with Admin privileges) For a long time now I have noticed that my customised power-saving settings and sounds scheme are not being "remembered" by Windows. They will be OK for a while, and then I will realise that they have both gone back to the default settings, although I cannot say if it is simultaneous. I have looked at HKEY_Current_ User/Control Panel/PowerCfg/Power Policies and there are 20 Policies listed - the 6 default settings and 14 customised ones (#6 is missing)! However, if I go to Control Panel/Power Options I am only offered tw...

formulas #6
I am trying to calculate the following: =SUM(F41-25)*5%+2*1.17 f41 - 33.99 I keep on getting 2.79 when the answer is 2.86. Can someone let me in on the right format to calculate this formula. thanks Hi! Try this: =(SUM(F41-25)*5%+2)*1.17 Returns: 2.865915 Biff "Bacchus" <kdoye2112@hotmail.com> wrote in message news:LYadnTzuQrHqv9_eRVn-sg@rogers.com... >I am trying to calculate the following: > > =SUM(F41-25)*5%+2*1.17 > > f41 - 33.99 > > I keep on getting 2.79 when the answer is 2.86. Can someone let me in on > the right format to calculat...

Restoring Settings???
Ok, I am still working on this matter where I try to open a link from existing online app, and the window is still minimized---not max as was the case usually. This problem seemed to develop after I up'd IE8/YM based on claim I needed to upgrade for better protection. When I went into log files, I saw a descript on "faulty app" for both IE8 and YM. I also found something on restoring setting to previous time. I remember doing this with the old "GoBack" program which I found very satisifying and easy to use. But I have never attempted this with Vista. I would r...