Getting the average from a column using date constrants

Using Column A (Date) Formatted Monday, February 1, 2009.
I want to grab the Average of column Z (percentage) where the Month of the 
row is February.
In a spreadsheet with 365rows the result set should be the average of 28days.

I have read many posts, tried many examples, 
Below is the current formula I am working with
=AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH(10),MainSheet!Z3:Z100)

I am getting a #DIV/0!
I am only testing this on 100 rows as the year isn't complete, as such there 
is 365 days of data.

Any help would be greatly appreciated...
0
Utf
4/20/2010 1:37:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

8 Replies
907 Views

Similar Articles

[PageSpeed] 10

>Using Column A (Date) Formatted
>Monday, February 1, 2009.
>In a spreadsheet with 365rows the result set
>should be the average of 28days.

So, you want the average for February 2009?

>=AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH(10),MainSheet!Z3:Z100)

Are you using Excel 2007?

Try this (Excel 2007)...

Use cells to hold the date boundaries.

AB1 = 2/1/2009
AC1 = 2/28/2009

=AVERAGEIFS(MainSheet!Z2:Z100,MainSheet!A2:A100,">="&AB1,MainSheet!A2:A100,"<="&AC1)

-- 
Biff
Microsoft Excel MVP


"Trevor." <Trevor.@discussions.microsoft.com> wrote in message 
news:860CE190-DE24-45AD-853F-A5E323DADD00@microsoft.com...
> Using Column A (Date) Formatted Monday, February 1, 2009.
> I want to grab the Average of column Z (percentage) where the Month of the
> row is February.
> In a spreadsheet with 365rows the result set should be the average of 
> 28days.
>
> I have read many posts, tried many examples,
> Below is the current formula I am working with
> =AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH(10),MainSheet!Z3:Z100)
>
> I am getting a #DIV/0!
> I am only testing this on 100 rows as the year isn't complete, as such 
> there
> is 365 days of data.
>
> Any help would be greatly appreciated... 


0
T
4/20/2010 2:28:05 AM
First, did you intend to use MONTH(10) in your formula?  That's going to get 
you the month of 1/10/1900, or 1.  Also, you have a different sized range for 
the Z column than for the A column

You can use an SUMPRODUCT()s to get there - this one asks for month 2

=SUMPRODUCT(--(MONTH(A2:A100)=2),Z2:Z100)/SUMPRODUCT(--(MONTH(A2:A100)=2))


"Trevor." wrote:

> Using Column A (Date) Formatted Monday, February 1, 2009.
> I want to grab the Average of column Z (percentage) where the Month of the 
> row is February.
> In a spreadsheet with 365rows the result set should be the average of 28days.
> 
> I have read many posts, tried many examples, 
> Below is the current formula I am working with
> =AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH(10),MainSheet!Z3:Z100)
> 
> I am getting a #DIV/0!
> I am only testing this on 100 rows as the year isn't complete, as such there 
> is 365 days of data.
> 
> Any help would be greatly appreciated...
0
Utf
4/20/2010 4:13:01 PM
I guess I wasn't clear...
I have a excel document that contains 13 sheets, 1 Main Data sheet, then a 
sheet for each month. In the main sheet I track input data for every day of 
the year, that shows among other things what percentage of the days orders 
were pickup, walking or delivery. I am trying to create a formula that will 
populate a cell in each of the month's sheet, that shows what the average 
daily percentage was for delivery. To achieve this I need to grab the 
appropriate Monthly data from Column Z (Delivery %). To ensure I only grab 
the values that relate to said month I need to constrain the subset of column 
Z against column A (which stores a long date) to ensure the appropriate Z 
column records are for the month at hand.

While I think this is clear, I also accept that it might just sound like 
junk...Any help is greatly appreciated.

Thanks,

Trevor.


"T. Valko" wrote:

> >Using Column A (Date) Formatted
> >Monday, February 1, 2009.
> >In a spreadsheet with 365rows the result set
> >should be the average of 28days.
> 
> So, you want the average for February 2009?
> 
> >=AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH(10),MainSheet!Z3:Z100)
> 
> Are you using Excel 2007?
> 
> Try this (Excel 2007)...
> 
> Use cells to hold the date boundaries.
> 
> AB1 = 2/1/2009
> AC1 = 2/28/2009
> 
> =AVERAGEIFS(MainSheet!Z2:Z100,MainSheet!A2:A100,">="&AB1,MainSheet!A2:A100,"<="&AC1)
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 

0
Utf
4/21/2010 12:48:01 AM
Ok, what version of Excel are you using?

-- 
Biff
Microsoft Excel MVP


"Trevor." <Trevor@discussions.microsoft.com> wrote in message 
news:372CF29E-B98D-4216-8CBD-29E554A21868@microsoft.com...
>I guess I wasn't clear...
> I have a excel document that contains 13 sheets, 1 Main Data sheet, then a
> sheet for each month. In the main sheet I track input data for every day 
> of
> the year, that shows among other things what percentage of the days orders
> were pickup, walking or delivery. I am trying to create a formula that 
> will
> populate a cell in each of the month's sheet, that shows what the average
> daily percentage was for delivery. To achieve this I need to grab the
> appropriate Monthly data from Column Z (Delivery %). To ensure I only grab
> the values that relate to said month I need to constrain the subset of 
> column
> Z against column A (which stores a long date) to ensure the appropriate Z
> column records are for the month at hand.
>
> While I think this is clear, I also accept that it might just sound like
> junk...Any help is greatly appreciated.
>
> Thanks,
>
> Trevor.
>
>
> "T. Valko" wrote:
>
>> >Using Column A (Date) Formatted
>> >Monday, February 1, 2009.
>> >In a spreadsheet with 365rows the result set
>> >should be the average of 28days.
>>
>> So, you want the average for February 2009?
>>
>> >=AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH(10),MainSheet!Z3:Z100)
>>
>> Are you using Excel 2007?
>>
>> Try this (Excel 2007)...
>>
>> Use cells to hold the date boundaries.
>>
>> AB1 = 2/1/2009
>> AC1 = 2/28/2009
>>
>> =AVERAGEIFS(MainSheet!Z2:Z100,MainSheet!A2:A100,">="&AB1,MainSheet!A2:A100,"<="&AC1)
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
> 


0
T
4/21/2010 1:05:01 AM
NUTS!!!!

Excel 2007.

T.

"T. Valko" wrote:

> Ok, what version of Excel are you using?
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Trevor." <Trevor@discussions.microsoft.com> wrote in message 
> news:372CF29E-B98D-4216-8CBD-29E554A21868@microsoft.com...
> >I guess I wasn't clear...
> > I have a excel document that contains 13 sheets, 1 Main Data sheet, then a
> > sheet for each month. In the main sheet I track input data for every day 
> > of
> > the year, that shows among other things what percentage of the days orders
> > were pickup, walking or delivery. I am trying to create a formula that 
> > will
> > populate a cell in each of the month's sheet, that shows what the average
> > daily percentage was for delivery. To achieve this I need to grab the
> > appropriate Monthly data from Column Z (Delivery %). To ensure I only grab
> > the values that relate to said month I need to constrain the subset of 
> > column
> > Z against column A (which stores a long date) to ensure the appropriate Z
> > column records are for the month at hand.
> >
> > While I think this is clear, I also accept that it might just sound like
> > junk...Any help is greatly appreciated.
> >
> > Thanks,
> >
> > Trevor.
> >
> >
> > "T. Valko" wrote:
> >
> >> >Using Column A (Date) Formatted
> >> >Monday, February 1, 2009.
> >> >In a spreadsheet with 365rows the result set
> >> >should be the average of 28days.
> >>
> >> So, you want the average for February 2009?
> >>
> >> >=AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH(10),MainSheet!Z3:Z100)
> >>
> >> Are you using Excel 2007?
> >>
> >> Try this (Excel 2007)...
> >>
> >> Use cells to hold the date boundaries.
> >>
> >> AB1 = 2/1/2009
> >> AC1 = 2/28/2009
> >>
> >> =AVERAGEIFS(MainSheet!Z2:Z100,MainSheet!A2:A100,">="&AB1,MainSheet!A2:A100,"<="&AC1)
> >>
> >> -- 
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> > 
> 
> 
> .
> 
0
Utf
4/21/2010 3:09:01 AM
This still gives me Div/0.

I added the Mainsheet! before the cell names as it is across worksheets.
As for the variance in the row definitions, that was a typo when I rewrote 
my current formula, as was the Month value.

Thanks,

T.


"Duke Carey" wrote:

> First, did you intend to use MONTH(10) in your formula?  That's going to get 
> you the month of 1/10/1900, or 1.  Also, you have a different sized range for 
> the Z column than for the A column
> 
> You can use an SUMPRODUCT()s to get there - this one asks for month 2
> 
> =SUMPRODUCT(--(MONTH(A2:A100)=2),Z2:Z100)/SUMPRODUCT(--(MONTH(A2:A100)=2))
> 
> 
> "Trevor." wrote:
> 
> > Using Column A (Date) Formatted Monday, February 1, 2009.
> > I want to grab the Average of column Z (percentage) where the Month of the 
> > row is February.
> > In a spreadsheet with 365rows the result set should be the average of 28days.
> > 
> > I have read many posts, tried many examples, 
> > Below is the current formula I am working with
> > =AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH(10),MainSheet!Z3:Z100)
> > 
> > I am getting a #DIV/0!
> > I am only testing this on 100 rows as the year isn't complete, as such there 
> > is 365 days of data.
> > 
> > Any help would be greatly appreciated...
0
Utf
4/21/2010 3:12:01 AM
Ok, then you need to add another condition to the formula that looks for the 
specific category that you want averaged. Is there a column range that 
specifies the category?

Let's assume column B is the category.

AB1 = 2/1/2009
AC1 = 2/28/2009
AD1 = some category like Delivery

=AVERAGEIFS(MainSheet!Z2:Z100,MainSheet!B2:B100,AD1,MainSheet!A2:A100,">="&AB1,MainSheet!A2:A100,"<="&AC1)

-- 
Biff
Microsoft Excel MVP


"Trevor." <Trevor@discussions.microsoft.com> wrote in message 
news:4FF53E91-F7B8-4EA0-88A9-7E93AE8C64BF@microsoft.com...
> NUTS!!!!
>
> Excel 2007.
>
> T.
>
> "T. Valko" wrote:
>
>> Ok, what version of Excel are you using?
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Trevor." <Trevor@discussions.microsoft.com> wrote in message
>> news:372CF29E-B98D-4216-8CBD-29E554A21868@microsoft.com...
>> >I guess I wasn't clear...
>> > I have a excel document that contains 13 sheets, 1 Main Data sheet, 
>> > then a
>> > sheet for each month. In the main sheet I track input data for every 
>> > day
>> > of
>> > the year, that shows among other things what percentage of the days 
>> > orders
>> > were pickup, walking or delivery. I am trying to create a formula that
>> > will
>> > populate a cell in each of the month's sheet, that shows what the 
>> > average
>> > daily percentage was for delivery. To achieve this I need to grab the
>> > appropriate Monthly data from Column Z (Delivery %). To ensure I only 
>> > grab
>> > the values that relate to said month I need to constrain the subset of
>> > column
>> > Z against column A (which stores a long date) to ensure the appropriate 
>> > Z
>> > column records are for the month at hand.
>> >
>> > While I think this is clear, I also accept that it might just sound 
>> > like
>> > junk...Any help is greatly appreciated.
>> >
>> > Thanks,
>> >
>> > Trevor.
>> >
>> >
>> > "T. Valko" wrote:
>> >
>> >> >Using Column A (Date) Formatted
>> >> >Monday, February 1, 2009.
>> >> >In a spreadsheet with 365rows the result set
>> >> >should be the average of 28days.
>> >>
>> >> So, you want the average for February 2009?
>> >>
>> >> >=AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH(10),MainSheet!Z3:Z100)
>> >>
>> >> Are you using Excel 2007?
>> >>
>> >> Try this (Excel 2007)...
>> >>
>> >> Use cells to hold the date boundaries.
>> >>
>> >> AB1 = 2/1/2009
>> >> AC1 = 2/28/2009
>> >>
>> >> =AVERAGEIFS(MainSheet!Z2:Z100,MainSheet!A2:A100,">="&AB1,MainSheet!A2:A100,"<="&AC1)
>> >>
>> >> -- 
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >
>>
>>
>> .
>> 


0
T
4/22/2010 1:12:32 AM
Guys,

  Any help on this?

Thanks,

T.


"Trevor." wrote:

> This still gives me Div/0.
> 
> I added the Mainsheet! before the cell names as it is across worksheets.
> As for the variance in the row definitions, that was a typo when I rewrote 
> my current formula, as was the Month value.
> 
> Thanks,
> 
> T.
> 
> 
> "Duke Carey" wrote:
> 
> > First, did you intend to use MONTH(10) in your formula?  That's going to get 
> > you the month of 1/10/1900, or 1.  Also, you have a different sized range for 
> > the Z column than for the A column
> > 
> > You can use an SUMPRODUCT()s to get there - this one asks for month 2
> > 
> > =SUMPRODUCT(--(MONTH(A2:A100)=2),Z2:Z100)/SUMPRODUCT(--(MONTH(A2:A100)=2))
> > 
> > 
> > "Trevor." wrote:
> > 
> > > Using Column A (Date) Formatted Monday, February 1, 2009.
> > > I want to grab the Average of column Z (percentage) where the Month of the 
> > > row is February.
> > > In a spreadsheet with 365rows the result set should be the average of 28days.
> > > 
> > > I have read many posts, tried many examples, 
> > > Below is the current formula I am working with
> > > =AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH(10),MainSheet!Z3:Z100)
> > > 
> > > I am getting a #DIV/0!
> > > I am only testing this on 100 rows as the year isn't complete, as such there 
> > > is 365 days of data.
> > > 
> > > Any help would be greatly appreciated...
0
Utf
4/23/2010 3:54:01 PM
Reply:

Similar Artilces:

Stacked Column Graph
I have data that counts in two separrate ways. How can I put two stacked columns next to each other on the same graph (X is in days, Y1 is # of incidents, Y2 is currently an average of days used for a line above the stacks). Hi Tim, Have a look at the resources referred to by Jon. http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html Cheers Andy Tim wrote: > I have data that counts in two separrate ways. How can I put two stacked > columns next to each other on the same graph (X is in days, Y1 is # of > incidents, Y2 is currently an average of days used for a line a...

Using TM Interscan as a smart host for outgoing mail
Hi All, I'm trying to set up my SMTP Gateway for outgoing mail, in addition to inbound. I have configured the gateway as a smart host on my Exchange 2000 box by going to the properties of my SMTP connector. Additionally, on Trend Interscan E-mail Virus Wall v3.53 I have allowed outbound mail. The options are (my config in brackets); Enable Outgoing Mail processing (ticked) Specify the IP address of any mail server that will be sending mail to the InterScan machine (192.168.1.1) Specify how outgoing mail will be delivered; Either forward mail to SMTP server or Use DNS (use DNS selec...

Can I convert columns to rows?
I need to convert address information, which is on word and listed vertically, to an excel spreadsheet, and have the information list out horizontally. Is this possible to do? I am familiar with the basics of each program, but using "range,formula,value" options in excel confuses me. Here's an example: (My data in word) Mel's Tire Store, Inc. 300 Culbertson Avenue Worland, WY 82401 (307) 347-3601 Need to copy and paste so data spreads horizontally in Excel, under these colum headings: "Business Name" "Address" "City&qu...

Windows 7 upgrade of XP Pro with IE 8 and getting Certificate Erro
My PC was recently upgraded from XP Pro with Windows 7 Pro and I also had IE running on my machine under XP. now when I browse to many sites I am getting a certificate warning message in IE. I can choose to continue on to the site (even though it says that is not recommended.). But this is VERY ANNOYING! Pleas help! I have tried adjusting many different IE settings, and verified the date-time is correct on my PC. This is happening on valid web sites. So far not very happy with this upgrade! Below is the error message I am seeing in IE: -----------------------------------...

which should I use: Outlook or ACCESS?
I'm a long-time ACT user and want to change. With 5,000 contacts I want to see contacts and their respective histories, todo's, calls, meetings, attachments, etc.. ... and i'd like to use template to write letters, emails, etc. I'm using Outlook for my currently over 5'500 contacts. If there's something I want to write about a contact and there's no such field (like history) I use the big comment field. If you need to do more things (what Outlook doesn't support) go with Access. Or you could start with Outlook and later export the data if you really ne...

Excel 2003: How to make transparent columns in Excel chart?
If you create a bar plot froma given dataset you can format the columns by right clicking and choosing the desired options. In the tab that opens there is a slider which is supposed tho set the level of transparency of the column (selceted area). But so far i couldn't find a way how to use this slider. I know that there is an alternative way to get transparent bars by creating a rectangular object formating it and the use copy -> paste. But i wonder what is the slider for if you can't use it? Does anybody know have an idea? Cheers, Thomas ...

Reset recently used files in Publisher to higher number
I want to set the number of recently used files in Publisher to nine from the four that it shows me at present. I can't find anywhere to change this. I'm using Publisher 2003. I don't think you can do that in Publisher but I'm sure that someone will chirp up if I'm mistaken. -- JoAnn Paules MVP Microsoft [Publisher] "Ken" <Ken@discussions.microsoft.com> wrote in message news:07E5ABDF-9C44-4091-8620-1AA38931EF91@microsoft.com... >I want to set the number of recently used files in Publisher to nine from >the > four that it shows me at prese...

Free 'absolute beginners' guide for making charts using Excel
If you are want the ABC's of making charts and a new way to look at the chart-making process, try this free book at http://www.vgupta.com ...

data sort is not including all columns in sort
I have a list with name, company, address, city.....etc. When I click on data sort (i do check the box to include column headings) and do a sort by say address, it will sometimes exclude a column such as name from the sort. It does not always do this. Is this a glitch? Do I need to highlight the entire list before I sort? Also, sometimes if I just use the Ascending or Descending icon rather that going to data, sort it will only sort that column and not the rest of the data with it. Those are the reasons that I don't trust excel to guess at the range I want sorted. I'll...

Date
Hello, I am tracking the response from suppliers, the status is: "On time" or "Late". [D3Date] is I received the response from suppliers. =IIf([D3Date] Is Null,IIf(Now()-[issueDate]>1,"Late"," "),IIf([D3Date]-[IssueDate]>1,"Late","On Time")) When I issue a ticket, shold be reponse in the following work date, if not is "Late", But if I issue on Friday, then the following work date is Monday, then the expression doesn't work right, how can I avoid the Staturday and Sunday. Thank you! ...

VB.net select column and format
Hi, I am using VB.net 2005 with Excel 2003. I need to select a single column and apply formating to it. Can someone please explain how to do this? Thanks, What sort of formatting do you mean? The basic code would be something like Dim RangeToFormat As Excel.Range Dim WS As Excel.Worksheet RangeToFormat = DirectCast( _ XLApp.Workbooks("Book1.xls"). _ Worksheets.Item("Sheet1"), Excel.Worksheet). _ Range("A1").EntireColumn WS = RangeToFormat.Worksheet RangeToFormat = _ XLApp.Intersect(WS.UsedRange, RangeToFormat) RangeToForma...

how to protect appointed rows or columns to use the mouse and keyboard
hzh, You could include some text in your post. Maybe then we could figure out what you mean by "appointed"?? "To use the mouse and keyboard" makes absolutely no sense whatsoever. Maybe you're a new poster??? If so, take a look here before reposting: http://www.cpearson.com/excel/newposte.htm Most anything that could be asked in the newsgroups has already been answered. Take a look here and you may just find what you're looking for (whatever that is)?? http://tinyurl.com/29pby John "hzh" <hzh62@163.com> wrote in message news:7A2382EE-00E6-4679...

Word 2007 how to get data from forms to Excel
I have forms that have been filled out by various people and would like to export the data into an Excel worksheet. This seemed straightforward in Word 2003, but I cannot get it to work in Word 2007. The actual form is a Word 2003 document. When I save the file as a txt file (after Word options / Advanced / Preserve fidelity / check "save form data as delimited text file") it creates the delimited file, but it gets overwritten when I close the original filled form. Any ideas? Thanks. I am having a similar problem, but the "save form data as delimited text...

why csv fields converted into dates
Hi I have a csv file with fields that look like this ,"20-10", this is being interpreted as a date when i open it in Excel. what do i need to do with this filed in order to make Excel consider it a text? I tried replacing the double quotes with a single quote like this: ,'20-10', but that makes the single quote appear in the cell in Excel, which is not what i want. please help. thanks hilz wrote: > I have a csv file with fields that look like this > ,"20-10", > this is being interpreted as a date when i open it in Excel. Hmm, that&#...

Use Outlook 2000 to retrieve Hotmail email?
Can someone tell me how to Use Outlook 2000 to retrieve Hotmail email? It keeps asking for a POP3 account info, when hotmail uses HTTP instead, very frustating. Thanks I don't think you can do this - you need to use either Outlook Express or Outlook XP (2002). --Dean. "John Luton" <JL112055@yahoo.com> wrote in message news:39e901c355aa$b1e17550$a001280a@phx.gbl... > Can someone tell me how to Use Outlook 2000 to retrieve > Hotmail email? It keeps asking for a POP3 account info, > when hotmail uses HTTP instead, very frustating. Thanks ...

getting all data about search subject
I have a client that had CRM installed on a server and migrated their ACT database over to it(i came into this on the tail end so i didn't set it up)--whenever they do an account lookup they want to get all the info surrounding that client--emails, images , past correspondances, invoices, quotes etc... they want all this in one click so to speak---in reading these posts two things popped out at me "searchpac 360" and"only the user can bring up what they own or produced" since this firm has several employees on the system all saving and sending info to and abou...

attach an excel workbook using send object method
I am sending emails from Access using SendObject and would like to attach an Excel workbook to the email. Any suggestions? -- Dar Hi Dar, SendObject will not work in this case, since it is only designed to send an object *within* the database (table, query, report, etc.). If you are using Outlook, you can try the method that I show on page 19 of a Word document that I call Access Links.doc. You are welcome to download a zipped copy, here: http://www.accessmvp.com/TWickerath/ Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/htm...

Column Sum in Footer
Hi, I have created a Tabular report that lists names and weights for different people. I want to create a stand-alone field in the page footer that will display the SUM of all the weights on the report. Can somebody please help me? (I'm using Access 2000 btw) I have tried creating a text box with the Control Source property set as "=Sum(weight)". However, this just produces an error. Thanks in advance. Julie Smith wrote: >Hi, >I have created a Tabular report that lists names and weights for different >people. I want to create a stand-alone field in the page f...

Insert date en time with onchange function
Hi, I have a ntext field in a service indenity . When it is edited i want automaticly insert date en time. Can anyone help? Kind regards Maarten Kievit You may review this website on using Javascript to get time. http://www.tizag.com/javascriptT/javascriptdate.php In your onChange event for that field, you may add var currentTime =3D new Date() var month =3D currentTime.getMonth() + 1 var day =3D currentTime.getDate() var year =3D currentTime.getFullYear() crmForm.all.[Your Field].DataValue =3D month + "/" + day + "/" + year; hope this helps. Darren Liu, Microso...

How to group similar column titles together????
I have an excel spreadsheet with 6 column titles as follows: 1st column title = ob1 2nd column title = ob2 3rd column title = ab1 4th column title = ob3 5th column title = ab2 6th column title = ob4 I have about 200 lines of information on each column. I want to run a macro / function on this spreadsheet and my spreadsheet should regroup similar titles together as follows: 1st column title = ob1 2nd column title = ob2 3rd column title = ob3 4th column title = ob4 <blank column> 5th column title = ab1 6th column title = ab2 Can someone think of any way to do this? (A macro ...

Adding using conditional cell
I have a two cells one with a 'number' value and another with a 'text' value i would like to have the number cell add up based on the text value. ie. 1 2 3 4 A 2 S B 2 S C 3 SM So then, because row A and B's text is S they will be added together to = 4. is there a way where i can make a formula or a table to calculate this? Z First, sort your data by that second column, so all the S's are grouped together. Then add a single header row at the top of your data. Select that range (all the rows and all the columns you need) and do:...

Inventory report that shows how much of an item was used over time
Howdy. Not overly well-versed in how to pull various reports, but we are looking to find a report that will show us how much of an item was used (in assemblies) over any given time period. Is this something that is found in Reports or would it be a Smartlist view. And, how do we get there? Thanks in advance for any help! mv ...

How do I use auto fill to add information from an existing source?
I am trying to figure out how to use some type of auto fill. What I have to do is put in a customer name and then I need to have the address, city, state, zip and phone number automatically fill in. How would I do that? Assume Sheet2's cols A to F is the existing reference, where col A contains the names, cols B to F contain the other info, eg: address, city, state, zip and phone number In Sheet1, you have the names listed in A2 down Put in B2: =IF($A2="","",VLOOKUP($A2,Sheet2!$A:$F,COLUMNS($A:A)+1,0)) Copy B2 across to F2, fill down as far as required -- Max ...

julian date #3
Is there a formula or line of code that, in a date cell, would automatically convert a date entered in the 07/16/04 format into the 04188 julian format? thank you. You can't have a formula in a cell and make an entry in it - the formula is overwritten. You can use an event macro: Put this in the worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim nYear As Long Dim nDays As Long With Target If .Count > 1 Then Exit Sub If Not Intersect(.Cells, Ra...

Opening an old Word document, date changed to current date
When I open old Word documents, the date gets automatically changed to the current date and I can't view the date that the document actually had. I am using MS Office 2007, but the old document was created in Word 2003. I can see the date that the file was last modified in the details of the file, but I cant guarantee that the date of the document is the same as the date last modified for the file. Press Alt+F9 to toggle the field code display and edit the DATE field code to read CREATEDATE. Then Alt+F9 to toggle back and F9 to update. The date displayed will be the date th...