retrieve an AVERAGE of a range using VLOOKUP to locate start/end d

Can I calculate the average of a range based on VLOOKUP? In other words, can 
I get Excel to "look up" the respective START / END dates of an investment 
position listed in Sheet 1 and then go and locate those 2 distinct cell 
values in in a different sheet (Sheet 2) utilizing VLOOKUP function and then 
return the calculated AVERAGE of the cells within that range back into Sheet 
1?
Appreciate any thoughts if doable in Excel in any way?
0
Utf
11/23/2009 11:49:01 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
2242 Views

Similar Articles

[PageSpeed] 5

tAKE A LOOK AT AverageIFS and see if that does what you want.   If it were 
me, I'd probably enter the formula manually, record the result from there and 
put that in my code.
-- 
HTH,

Barb Reinhardt



"Sol" wrote:

> Can I calculate the average of a range based on VLOOKUP? In other words, can 
> I get Excel to "look up" the respective START / END dates of an investment 
> position listed in Sheet 1 and then go and locate those 2 distinct cell 
> values in in a different sheet (Sheet 2) utilizing VLOOKUP function and then 
> return the calculated AVERAGE of the cells within that range back into Sheet 
> 1?
> Appreciate any thoughts if doable in Excel in any way?
0
Utf
11/24/2009 2:29:01 AM
Reply:

Similar Artilces:

How do I name a range and then have that range be dynamic?
I want to select the entire contents of a worksheet, but that data set will change each time I open it as records will be added to it every day. I want to always choose all records, and there will never be any non-contiguous rows in the data or blank rows in it. So I can make a direct call to it and always get all of it by way of the named range. If A1 is the header for example for the first column you can use Range("A1").CurrentRegion.Select The same as Ctrl * -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "CellShocked" <ce...

Always uses default account when sending mail
I have several accounts configured in Outlook 2003 SBE. If I have composed a message and I use the Accounts button to change the account used. Outlook then displays which account will be used for sending the message; this is another than the default one. When I send the message, it still uses the default account to send it, I see this when I test it. What is the matter? Cheers, Thorbjorn I appear to be having a similar problem under Outlook 2003 on a new XP system. I've configured in multiple email POP accounts. Usually if I send just one email at a time, it will retain the default...

Using Workflow to Notify Salespeople of Open Cases
I follow the instructions from the article : http://www.microsoft.com/dynamics/crm/using/services/notifyopencase.mspx But the mail is send to the owner of the Case ( the CSR ) and not to the owner of the Account which is regarding to the Case ( the Salespeople ). Could anyone help ? ...

Office wont start after update!
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Ok, so i installed office and had all the updates done..and when i click on word it opens, then i do the last update which was on april 14 i believe and after that it doesnt open. it keeps saying register online (that box where u can click "learn more" or "register online") i click finish it opens up the update screen, there are no more updates so i close that and word never opens nor does excel or anything else. whats the problem? installed office and it works fine, then i did one update and it works fine, but once...

How to retrieve the string from dialog box
Hi All, Does anyone know how to retrieve a string in IDC_STATIC on a dialog box? I have to print out a report with an application version at the bottom line for users to be aware of it in my application. I don't know how to retrieve the string in IDC_STATIC control on a dialog box. It would be very nice that someone let me know how to do that without calling the dialog box. TIA Hwasoo Lee Back 9 wrote: > Hi All, > > Does anyone know how to retrieve a string in IDC_STATIC on a dialog box? > I have to print out a report with an application version at the bottom line &g...

retrieve an AVERAGE of a range using VLOOKUP to locate start/end d
Can I calculate the average of a range based on VLOOKUP? In other words, can I get Excel to "look up" the respective START / END dates of an investment position listed in Sheet 1 and then go and locate those 2 distinct cell values in in a different sheet (Sheet 2) utilizing VLOOKUP function and then return the calculated AVERAGE of the cells within that range back into Sheet 1? Appreciate any thoughts if doable in Excel in any way? tAKE A LOOK AT AverageIFS and see if that does what you want. If it were me, I'd probably enter the formula manually, record the resu...

Do I have to enter a start AND end date for a task in Outlook?
I am getting a conflict when trying to sync between Outllook 2003 and Blackberry Desktop Manager. Error msg talks of a task having a negative duration and to check the start/end dates/times. when setting the dates, use the start date button. One click on a dtae there will enter the same data for start date and due date. If you want to have a period of time between the dates, use the start date button and wipe a period with your mouse on the little date navigator. -- Regards Judy Gleeson MVP Outlook www.judygleeson.com www.deskdoctors.com Are you sick of bad email practice? Get a c...

Mail sent using MailMessage class displays "TO" and "CC" recepients in the message body when sent to large number of recepients
I'm using System.Web.Mail to send an email message from an ASP.NET web page. This is the main section of the C# code. //************* start code ***************************** string[] mailHost = (System.Configuration.ConfigurationSettings.AppSettings.GetValues("mailserver")); //Get data from page string toAddress = ((tbTo.Text==null)||(tbTo.Text.Equals(String.Empty)))?String.Empty:tbTo.Text.Trim(); string ccAddress = ((tbCC.Text==null)||(tbCC.Text.Equals(String.Empty)))?String.Empty:tbCC.Text.Trim(); string subjectLine = ((tbSubject.Text==null)||(tbSubject.Text.Equals(String.Em...

Conditional formatting in range
I have a range named "Range1" and have a conditional formatting putting letters into red colour if the condition meets "K6=1" When ever I insert a row between the range the conditional formatting formula is not copying, every time i have to use the format painter and copy it. Is there any code, any body pls help me thanks Go to: Tools<Options<Edit tab Make sure there is a check mark next to "Extend data range formats and formulas". Hope this fixes your problem. -- -Brigette "RE: VLOOKUP fORMULA" wrote: > I ha...

Upgrading to Win2K3 and Exchange 2003
Can anyone please advise on what additional functionality I would be missing out if I keep my existing desktop email client on a mixture Outlook 97/2000 and XP. I am not planning a new Office2003 roll-out, but would very much like to explore the possibility of upgrading our existing NT4.0/Exchange 5.5 to Exchange 2003. thanks in advanc Na ...

summing part of cells in a range
hi all, in the range myRange each cell contains 10 digits, how to sum the first 4 digits to the left of each cell in this myRange, instead of adding each cell alone like: =left(A1,4)+left(A2,4)+left(A3,4)+... thanks for any help Hi =SUMPRODUCT(--LEFT(A1:A10,4)) -- Regards Frank Kabel Frankfurt, Germany "excelFan" <excelFan@discussions.microsoft.com> schrieb im Newsbeitrag news:4B2EAEDB-F3DD-4A73-BD88-EDBB3BA0E992@microsoft.com... > hi all, > in the range myRange each cell contains 10 digits, how to sum the first 4 > digits to the left of each cell in this myRa...

Problems using exchange server 5.5 sp3 with outlook 2003
we are using exchange server 5.5 sp3 with outlook 2003 and experiencing the following problem: The problem is that once the outlook shared contact file was placed on a shared server, employees discovered that when an invitation is sent out to other members it would not come up as a "decline" or "accept" message but just as a plain text message informing them of a meeting. Any suggestion? Thanks Pete wrote: > we are using exchange server 5.5 sp3 with outlook 2003 and > experiencing the following problem: > > The problem is that once the outlook shared contact...

Why won't Publisher let me use my OMR bubble font?
I am creating scanable forms to use with an OMR program. The program comes with an OMR bubble font (i.e. bubbles with little letters or numbers in them). The font is installed on my computer and every other program seems to have no problem using it but when I try to use it in publisher in a text box the text simply reverts to some other font (normally Times New Roman). I really would like the design capability of publisher but if it won't use my font it will be a real drag. When does it revert to another font? After exiting the text bos, after printing, etc? -- JoAnn Paules MVP ...

Retrieve mouse position
Hi, I am writing an app in which I need to retrieve the mouse coordinates even when it mouse is outside the main frame. How do I do it? Thanks! xg >Hi, I am writing an app in which I need to retrieve the mouse coordinates >even when it mouse is outside the main frame. How do I do it? Thanks! Have you tried calling GetCursorPos? If you want to know when the mouse is moving I think you'll need to use a hook - have a look at SetWindowsHookEx WH_MOUSE. Dave IN addition to David's answer, you have not stated the problem you are trying to solve. For examp...

How to make a stacked bar chart using time?
Hi, I have the following data to plot on a stacked bar chart, and displayed as time. Dr X Dr Y Dr Z 02:03 03:00 01:00 00:20 02:00 00:30 00:40 01:00 00:30 02:45 01:00 00:15 02:10 08:13 07:00 02:00 This is time in hours and minutes. I need the result to look like this... Dr.X bar [02:03][00:20][00:40][02:45][00:15][02:10] Dr.Y bar [03:00][02:00][01:00][01:00] Dr.Z bar [01:00][00:30][00:30] where [02:03] represents a colour to represent 2 hrs & 3 mins etc. If I just select the Dr. X data I can get a stacked chart, but as soon as I add either Dr. Y or Dr. Z data the whole chart ...

Retrieving comment using range name
I have singular cells in different places referenced by their range names. Is there a way to get the comments corresponding to the referenced ranges (singular cells)? Thanks in advance, Tomek Set testrng = Range("A3:A4", "C3:C4") comment1 = testrng(1).Comment.Text .... commentn = testrng(n).Comment.Text Stefi „topola” ezt írta: > I have singular cells in different places referenced by their range > names. Is there a way to get the comments corresponding to the > referenced ranges (singular cells)? > Thanks in advance, > Tomek > > did yo...

Averaging cells not connected
Iam somewhat new to Excell 2003. I've just learned how to average cells (& ignore zeros) that are connected, either row to row, or coloumb to coloumb. I need to know how to average cells (& ignore zeros or blank cells) that is a mix of connected cell and others that are not connected.....like; B5 C5 D5 B10 C10 D10 this format continues for several more rows. Not sure if I'll find this help screen again. If you don't mined, would you also email the results to: cusjan@yahoo.com Thanks. Try: =AVERAGE(IF(B5:D5<>0,B5:D5),IF(B10:D10<>0,...

vlookup for words content when there are duplicate values
Hi, Can somebody help me here. Thanks in advance. When I vlookup Material 1 to Material 2 for the Project No, I get the vlookup Project No. as 222-56 for all the duplicate Material values. How formula to use so that when there are duplicate values in Material2, I want to have vlookup searching to match Project No. containg "AA" as 1st choice ? (if no Project No. containing "AA", then looks for its content "AB" and subsequently looks for "AC" if there is no "AB"). ColumnA ColumnB ColumnC Material 2 Duplicate? Project No. 224410 duplica...

Template using Word
Outlook 2003, Word 2003 I want to use Word as my email editor, and also set up a standard template. This would achieve the following things: Use standard styles Use Word fields, so recipient name is filled in automatically Possibly use Word macros ...

Hlp! Insert Symbol based on Range
I am creating a report that includes weight, blood pressure, etc. and have to show a different picture (or symbol) that indicates Fine, Could be better, Needs attention, for example. So, I need a way to look at their result, compare it with normal ranges and then have the correct picture appear. Any ideas would be greatly appreciated! -- Thanks! Dee You likely could use an expression in the Picture property of an Image control in the repor; for examplet: =IIf([FieldName] Between "One Value" And "Another Value", "C:\FolderName\GoodPicture.bmp", IIf([F...

Average in Pivot Tables
Hi, I have created a table with the following data student id, exam name, score. I want to create the following pivot table | Exam name 1 | Exam name 2 --------------------------------------- student id1 | Score | Score student id2 | Score | Score student id3 | Score | Score --------------------------------------- Average Average Excel will only let me give the Grand total. Is it possible to get the average. The sum of the score is meaningless. With regards Constantijn Enders If you go to Field settings for your data fields and change S...

Retrieving messages
Help, I mistakenly restored all the defaults on Outlook (the computer did not crash) losing all the messages and address book. I have had to set up the application from scratch entering account details etc. Question is: are all the messages and contacts still held on the computer even the program needs to be set up again and, if so, how do I retrieve them. Any assistance greatly appreciated. Thanks. P89 If you made a backup of your system or My Documents, search for a file called Outlook.pst. If you have that you can import all the information. Al "peterson89" <peterson...

Simple average question
I'm sure this is a ridiculously simple question for most of you folks but I've been trying to find an answer for hours and hours. I have a column of values. In the next column I sinply want the average including data down to that row. So far I have been editing the formula manually but this gets old for several thousand values. In case my question is not clear I will show formulae I am using to get what I want: In column B starting with row 1 and moving down (the data are in column A): B1=AVERAGE(A1:A1) B2=AVERAGE(A1:A2) B3=AVERAGE(A1:A3) B4=AVERAGE(A1:A4) I guess I'm looking...

Dialog using Doc/View Arch
Hi All, I made some Dialog-based application without document/view architecture. I use to create Dialog class , and use to call(DoModal) from ChildView.cpp. Now I am trying to go with Doc/View Arch ,same dialog.. But , Do I need to create Dialog class? If yes ,from where should i call dialog (i mean from View Class)? Initially i use to manipulate Dialog data in dialog class created by me. Now with respect to Doc/View Arch where should i manipulate Dialog data , in doc class or dialog class ?? Dialog consist of Registration form which is filled by usr. Awaiting for ur reply... Rega...

using a date variable in query criteria
Hello I want to use date variables in the criteria of a query. When I put Between #1-1-07# and #1-30-07# it works but if I try to use a date variable it doesn't work. In the query by example view. The field in the database is a string but I convert to a date using Datevalue([work_order_received]). I want to calculate a date like this in the Field: dateback: datefrom: ( year(Date())-1 , 1 ,1) dateto: (year(Date())-1,12,31) When I try to use the variables (datefrom dateto) in the criteria statement this doesn't work ...