Can Age Ranges In One Field Calculate To Word Value In Another?
May 2, 2012
WORKSHEET RIGHT NOW:
Column A = date of birth --- formula being used: =TODAY()
Column B = age, calculated to years, months, days --- formula being used: =DATEDIF(C13,TODAY(),"y")&"y "&DATEDIF(C13,TODAY(),"ym")&"m "&DATEDIF(C13,TODAY(),"md")&"d"
What I need in Column C is the following:
If the age from Column B is from 0 to less than 2, I need it to say "Infant"
If the age from Column B is greater than or equal to 2 but less than 12, I need it to say "Middle"
If the age from Column B is greater than or equal to 12 but less than 18, I need it to say "Adol."
If the age from Column B is greater than 18, I need it to say "Young Adult"
I have hundreds of records of customer info and I am trying to categorize the job titles so that I can conduct some analysis with a pivot table. My problem is that there is about 20 different ways and variations of "manager" so Instead of autofiltering for "mrg." "Manager" "marketing manager" ect. and then replacing the text manually and using the enter and fill process (to create some standardization to compare "manager" to "director" to "c-level" to "consultant", etc.) and repeating this process over and over again...
I would like to create a VBA that would search the column "job title" for multiple text strings at once and if the text string was true in the cell then the cell would be replaced by a new text string "Manager". I was thinking a series of if functions within one vba but I am not sure if this is possible.
I have a pivot table that analyzes test results. Assume three fields: Test, Result and Failure. If a test resulted in a failure then there is 1 in this field; otherwise there is a 0. There are several test results for each test. I want to calulate the percentage of failures for each test. I have selected Test for Row Labels. I cannot figure out how to calulate the percentage. Basically, the formula equates to =SUM(Failures)/COUNTA(Test). In other words, for each test add the failures and divide by the total number of results for that test.
I have attached a simple test file. The data tab has two columns in the pivot range. The third column is _not_ in the pivot range, but is there to show the result I'm trying to get with the pivot.
The idea is to count the number of people whose entry dates are greater than 2/1/2009. My third data column gives a correct result, but a similar formula in the calculated field of the pivot table gives a completely wrong result (a date), where my goal is to generate a zero, a one, or a sum of zeros and ones.
It could be a formatting issue, but that would not explain why all the pivot results have the _same_ wrong answer.
I'm new to PTs and calculated fields, and expect I've made a naive mistake that an expert could easily spot.
I need to have a cell with a date in it then the next cell needs a formula to caluclate how many days it has been since that date???? I'm a real novice with excel.
I am looking to take information from a document emailed to me in word and then populate the specific ranges in my excel spreadsheet for invoices. What is the best method for doing this and how can i control where it comes from the position in the document and the range is going to.
I have a range of cells and when an amount is entered into a cell I would like it to pick which row to pick from and do the sum from that row. for eg if I was to put $201,000 I want it to pick the row with $200,000 to $540,000 range and use the sum from that row. eg below
I would like to use the following code to see if the word "reservoir" shows up in any of the rows in a certain column. If it does I want to insert the formula = SUM(D7:D257)*0.1 into cell C2 and if it doesn't then I want to put a 0 into cell C2. I've tried many routes but can't get past the error if it can't find the word.
I am staring at this excel spreadsheet blankly ... I am at a loss as to how to accomplish the detailed analasys but simple math this spreadsheet reequires.
The data consists of three columns pasted from a report that is exported as a .csv:
B Code C Start Time D End Time
The following analyses must be made on each row:
1. determine if the start time falls within the scheduled shift
i want to do is i have an excel spreadsheet...and i have two columns in there that are called Representative and one thats called Result....The Result column has Closed, Rescheduled, and Not Interested....so what i want to do, im looking for a formula that will calculate how many CLOSED are from a certain Representative....so if i have 5 Closes for John...im looking for a formula to calculate those 5 for John...but i dont know what formula nor how to use two diff. ranges with two diff. criterias....
How can I create a formula to add up how many cells in a column show a particular set of text? For instance; the column in my spreadsheet looks something like this:
MJR DML MJR RJG RJG MJR DML
I want to know how I can create a formula that will automatically tell me how many times "MJR" is listed; how many times "DML" is listed, and so on.
Need to calculate the number of days in December between two dates 11/20/2013 and 1/20/2014, how many days are in december? a formula that can calculate this?
I have a list of mileage rates that correspond with different date ranges. For example the mileage rate for March 19, 2008 – July 31, 2008 was 50.5 cents per mile. I have 11 different mileage rates / date ranges from 2000 onwards.
I would like to devise a way that I could input the date and the number of miles travelled into the spreadsheet, then Excel would check the date entered in order to choose the appropriate mileage rate and calculate the dollar amount based on the # of miles input.
Here are the date ranges and rates in the event that anyone is interested in putting something like this together. I would be grateful for an example for one or two of the rates though and hopefully could extrapolate from there.
I am trying to determine the total hours of downtime accumulated when there are 2 or more machines down during the same time period. To do this, I need to be able to determine if at any point there are 2 or more pieces of equipment down at the same time, and if this is true, how many hours were overlapped. The attached spreadsheet shows how the data is presented.
I am working on word documents that has financial numbers on them. I am copying word pages to excel to calculate the numbers by using formulas and etc. I will cut to the chase; is there a way or a macro to insert into an excel template workbook(.xltx) when we open the template there will be a command button to select the word document (which we want to copy its pages) to excel sheets. When a new page begins a new sheet will be created and the page will be pasted on the new sheet.
It is not important if it has to have a command button to select the word document it is ok to run the macro and select the word document and it does not has to populate sheets according to length of word document (I can create lost of free sheets on the template)
The main idea is to gain the time I spend on copying the word document to excel sheets. Some documents can be very long (100 pages). I am using MS Office 2007.
I triend to export data but excel does not allow me to select word documents.
loop that can calculate the median of dynamic ranges. I need to run through all columns and for each column find the the ranges (there are more ranges and they are seperated with a blank row) with numerical data and then calculate the median and thereafter continue down to find the next range with numerical data in that row and so on.
I have an excel program that is supposed to count word instances in a word document. I can't seem to find the right declaration for a word document. For example to declare a workbook in excel its
Dim wb As Work Book
I've tried
Dim doc As Word.Document 'or Dim doc As Word.Application
as shown in some of the forum posts, but an error user-type not defined keeps displaying.
In the following extract, I would like to have today's date placed into Col K when an entry is made into Col J.
Entry into Col J is from a validation list of 3 options. What I desire is that when one of these options is selected ("Complete"), then I would like today's date to be written into Col K.
Currently Col K has a validation list which comprises dates from 1st of each month from July to June.
I need a fixed date as text preferably, so was thinking the VBA function "Date" on a Change Workbook Sub.
The information is currently part of a Table in Excel 2007 that has about 500 records.
I have a macro that imports a report. If the Charge Type in column A is BTOREPLX, I need to prefill the field next to it in column B with the text "REPLX", e.g. "REPLXCDROM". I've attached an example of the report.
In building my pivot table my data that I want to show in the column area is showing up as rows stacked on top of each other. In the column section I'm trying to show Total Budgeted Amount next to Total Actual Amount but on the layout it's showing the two stacked on top of each other is there some kind of hidden key that I'm missing?
I have a simple formula ='info page'!b2&'info page'!b5 in places that on my sheet adds a company prefix to a item number, prefix in b2 and item in b5, we have a client that we cant use prefixes but now when I leave the field blank its giving a zero and thats not good either. I'm pretty sure an IFERROR or something will work but can get the syntax right.
I have a tab called 'Tab1' in which I have column called 'Col1' and 'Col2'.
I have another tab called 'Tab 2' with columns 'ColA' and 'ColB'. When I enter a text in Col1(Tab1) I would like that text to be compared to data in 'ColA' from Tab 2 and when match found then get the corresponding value from ColB and autopopulate it in Col2 of Tab1.
I am trying to count the number of times that data is in a field and in any field the date is greaert than today's date. This date field could be blank and that is where I am having the problem.
Is it possible to export Excel cell contents to Word fields in a protected Word document? For example...
What code would be needed to tell Excel to open up, copy and export the contents of A2 in the active sheet of a workbook to "Field 2" in a Word document named "Report 01" and then put the contents of B2 to "Field 2" etc?
Do both applications have to be opened up at the same time or is Excel able to open up Word on its own? Will the macro be able to....
1. Automatically open up the correct Word document?
2. Look ONLY in a certain folder for the "Report 01" Word document?
or
Bring up a "selection" box that allows you to select the document you wish Excel to export it's data to?
3. Close and save the Word Document without any user intervention?
I need to count how many times the word Test is in the range B4:H9 with
Range N2 = Test the formula below works if Test is only in the cell once.
=COUNTIF($B$4:$H$9,"*" & N2 & "*")
But I have data in cells like below, this is all in one cell, so how would I have it count all the times test is in the range when some cells have test 2 or more times in a single cell?
I need my macros to search for the word "Cancel" or "Cancelled" in columns "T" and "U". Once found, I need the macros to make that entire row an opaque shading.
There will be other wording in these cells that contain "Cancel" or "Cancelled". Is it possible for the macros to search in the sentence and find the words "Cancel" or "Cancelled"
