# Excel 2013 :: Calculating Count Of Persons Of Certain Ethnicity Relatively To Place Of Birth

Mar 16, 2014

I am Using Excel 2013 on windows 8 OS,

I have no programming background but I have an Idea about it and I try to write some VBA Macros.

I have 27 Excel files which contain each of them 257368 Row Concerning personal information about persons
the File Format is as Follows:

Place of Birth (POB); Name; Father's Name; Family Name; ID Number; Ethnicity,>>

## Excel 2013 :: Working With Tabs And Calculating Across Multiple Ones

Jan 6, 2014

I am working on a commission report for our veterinarians. I have a tab/sheet for each year, with a column for each month, a row for each of the veterinarian. Each vet is on the same row, year to year. I need to be able to calculate the %, year to date, for each vet, across the past 2 years. I can do the auto sum, but do not know how to do percentage. I have row 3, column b3 thru m3. I want to do 2012 and 2013. Each sheet is name the year.

## Calculating Age From Date Of Birth

Sep 16, 2009

how to calculate the age from the date of birth and was given these two responses

1) I may be way off here but all that coding seems a waste !

I have in cell A1 = BLANK <--- enter your DOB here

I have in cell A2 =NOW()

in cell A3 do = A2-A1

Format that to General

In Cell A4 do =A3/365

Format that to no deciamal places and you get the age - this one is adding a year to everyones age?

2) Alternatively, if the DOB is A1 :-

=INT(YEARFRAC(A1,TODAY(),1)) - This one does not work it is saying there is an error with the "yearfrac"?

It is also when I am trying to put these into a pivot table, the pivot table is listing these down to 5 decimal places( when I have formatted to zero)????

## Calculating Age When Given Only Date Of Birth

Sep 6, 2005

http://www.cpearson.com/excel/datedif.htm
--

"malvis" wrote:

> Is there a formula that can calculate age when given only Date of Birth?

## Excel 2013 :: Calculating Value Based On Multiple References And Dropdowns

Jul 19, 2014

Using Excel 2013.

I have two sheets in a workbook.

Sheet 1 has the following columns on it, with the following possible values.
People (Dropdown with value between 1 and 8)
Modifier 1 (Dropdown with Text 1, Text 2, Text 3)
Modifier 2 (Dropdown with Text 1, Text 2, Text 3)
Modifier 3 (Dropdown with Text 1, Text 2, Text 3)
Modifier 4 (Dropdown with Text 1, Text 2, Text 3)
Result

Sheet 2
Contains a set of values (Distance) that correspond to the value of the People dropdown (ie. People 1 = Distance 5, People 2 = Distance 6 and a table that looks like the following:

Mod 1
Mod 2
Mod 3
Mod 4

Text 1
0
0
0
0

Text 2
2
0.5
0.25
0.25

Text 3
7
1.75
0.875
0.875

What I want to happen is that the Result field value on table one is the result of:

Distance*(Modifier 1 + Modifier 2 + Modifier 3 + Modifier 4)

For example:

On sheet on I select People = 2, Modifier 1 = Text 3, Modifier 2 = Text 1, Modifier 3 = Text 1, Modifier 4 = Text 1 which I want to result in the following formula:

6*(7 + 0 + 0 + 0)

This might be exceedingly simple, but I just cannot wrap my head around how to do it.

## Calculating Individuals Exact Age Given Their Date Of Birth

Jan 21, 2004

I am looking for a formula that calculates an individuals exact age given their date of birth. That shows just the age in years.

## Count Of Persons Name In Range

Mar 11, 2008

I want to count how many times a name is listed in the first 5 rows of 36 columns.

## Excel 2013 :: Conditional Count If Formula

Apr 4, 2014

I am working in Excel 2013.

My objective is to count the number of "Horizontal-Horizontal" entries in Column D for a specific value in Column C, BUT (and this is where I am lost), taking into consideration only unique values in Column A.

So, for the highlighted red entry in my spreadsheet sample below, there are 2 entries of Horizontal-Horizontal for Column C value A2961. BUT since, Column A entries for A2961 are duplicates, I want to return a value of 1. Hope this makes sense.

This is my formula that is working for the first part of the equation. I need to add something to it to condition the count based on unique values in Column A.

=COUNTIFS(\$C\$2:\$C\$101192,C2,\$D\$2:\$D\$101192,"Horizontal-Horizontal")

A
B
C
D
E
F

4229532326
HORIZONTAL
A1657
Vertical-Horizontal
A1620
0

[Code] .........

## Excel 2013 :: Unique Count In Group SubTotal

Apr 16, 2014

Using Excel 2013.

Is it possible to get a unique count in a group subtotal though the elements in the group may not be unique using Pivot Tables w/o resorting to Power Pivot?

Ex

Group 123
Group 456

Are both members of NorthDivision

If someone is in Group 123 for 9 months, then Group 456 for 3 months, that membership should be reflected in the correct group accordingly.However, that person should only be counted once in the NorthDivision, not twice.

Output:

Group 123 0.75
Group 456 0.25
NorthDivision 1

At first glance, it appears easy, however, what if membership is only total 3 months?Then Group would increment 0.25, but I need NorthDivision to increment 1

## Excel 2013 :: Count Unique Entries In A Table That Match A Criteria?

Jul 11, 2013

2013

Current Year

Q1-13
Q2-13
Q3-13
Q4-13

Totals

Awarded
£19,000.00
£4,000.00
£3,250.00
£0.00
£26,250.00

[code].....

I need to count the number of unique companies that receive money within a specific QTR. I have made this simple example, I have a Table called Awards, with Headings for DATE, QTR, Company, Awarded, on one worksheet, that I need to feed the data into a summary on another worksheet. What formula using table heading can I use to achieve the answer 3 unique companies for Q1-13.

## Count Of Names By Date Of Birth (DOB)

Jun 26, 2008

I am using Office 2007 - Excel

I have a list of students with associated Schools
For each High School -Based on students Date of Birth (DOB) I am trying to get a count for:
Students UNDER 16 DOB before 8/25/91 and for each high school students over 16 DOB after 8/25/91

I thought subtototals, countif , maybe a pivot table to break down by school then do the 2 counts -
but I am at a loss for a formula to do the count

I have attached a sample file but the actual file has thousands of students and 23 different High schools which is why I was trying for subtotals or Pivot

## Converting Date Of Birth To 'Term Of Birth'

Feb 21, 2009

Is it possible to convert a students DoB into their term of birth (ToB).

I.e. a student has the DoB of 1/1/2001 which would mean their ToB would be 'Spring'

I suppose what I'm after is...
If a DoB falls into one of three groups
1/1/year to 30/4/year = 'Spring'
1/5/year to 31/8/year = 'Summer'
1/9/year to 31/12/year = 'Autumn'

The year of birth is irrelevant, it's the month which determines the 'term of birth' (Spring/Summer/Autumn).

## Excel 2007 :: Highlight Birth Dates That Are In Column Which Are Over Or Under Certain Age

Jan 20, 2014

I am setting up a Cattle management system in excel 2007. In the column F are the date of births for each individual animal. Example: F2 13/03/2013 F3 23/05/2013. I would then like to highlight the cells with the dates in to find all the cattle that are under the age of 16 months from the current date.

## Count If Formula And Place In A Graph

Oct 11, 2008

I have a spreadsheet of which i need to count instances on 2 COLS and place in a graph.

Example
D...................................... Col E
JO BLOGGS....................... ERROR
JO BLOGGS....................... ERROR
JO BLOGGS....................... SPOIL
JO BLOGGS....................... SPOIL
JANE DOE........................ ERROR
JANE DOE........................ SPOIL

I need it to count the errors Jo bloggs had so i can populate the chart, the chart would be no problem once i can separate the data as described. I would then have to do the same with Jane Doe.

## Count Total Number Of Saturdays In 2013

Apr 13, 2012

the closest appears to be the number of Fridays in a particular month.

What I need to calculate is the number of Saturdays in each year from 2013 through to 2022 (by year), and the same thing for number of Sundays. I presume that it will be 52, however if a year starts on a Sunday and it is a leap year then there could well be 53.

I tried =SUMPRODUCT(--(WEEKDAY(YEAR(C1)=7))) (where C1 has 2013) to try and calculate the number of Saturdays in 2013, but it didn't work.

## Excel 2013 :: Mail Merging From Excel Database To Outlook

Apr 15, 2014

Using EXcel 2013, Windows 8

I have an Excel worksheet with one column being e-mail addresses. Other columns are Christian names, etc

Ideally can I create a full Mail merge with Outlook using whatever data I want. But probably just e-mail address and Christian name?

Otherwise be able to send one e-mail to all the e-mail addresses, without a major re-type.

## Pop Up Appear If A Persons Name Appears More Than Once

Jun 20, 2008

I would like to have a list of names in column A1 Smith, A2 Jones, A3 Johnson etc. Everytime someones name appears in E1-E20 I would like the adjacent column next to that persons name to turn red. Example: I type Smith in E14 -> B1 turns red. If it's not too complicated it would be good if excel could recognize the name in any font,capitols etc.

Lastly if I could have a pop up appear if a persons name appears more than once in column E that would be great. Example type Smith in E1 and then when I type smith in E4 pop appears--Name already used once do you want to continue?

## Macro To Copy And Place Data To Specific Place

Feb 22, 2007

I am after a macro to do the following, my visual basic skills are very limited (non existant):- Look at the date in cell A1 on Sheet 'Live Report' and err 'remember it' Copy a range of cells from A3 to A10 on 'Live Report' Go to sheet 'Monthly Summary' and find the date that had been remembered previously (this date will be in column A on 'Monthly Summary' which will probably be a mixture of values and formulas). After the date has been found paste special and transpose the 'values only' copied range from 'Live Report' (A3 to A10) in column B on 'Monthly Summary' next to the date that has been found in Column A.

## Calculating Sum And Count Across Sheets?

Feb 23, 2014

I have a sheet that has products, purchaser, price. I need to use a formula in a table on a separate sheet that will give me the sum of the items purchased.

For Example:

Person X purchased 4 chickens at \$2 each. I need to plug a formula into the table that looks up by persons name, then the sum of chicken purchases.

## Two Different Persons Locking Cells?

Jul 16, 2013

Is it possible for 2 different persons to lock different cells on the same worksheet?

## Finding Symbols To Place In Excel

Oct 25, 2012

where i can find symbols to place in Excel..i am looking for a warning sign,home and a large hand with the palm facing down with a pointing finger.

## How Can I Make A Persons Name Read A Formula

Jul 28, 2009

If I type the name Joe Bloggs How can I make whenever i type that name the computer knows to retreive a number or formula from a cell on another or same sheet.

## Place Excel Workbooks Into Network Folder

Dec 12, 2012

I want to ask about the network folder. The current systems that i develop contains about 15 excel workbooks and every workbooks link with each other. If i place all the workbooks into the network folder, does all the formula, link and vba will be changed to network too?

Such as the formula: ='G:systems[workA.xlsm]A1.

Does it will change the path of the formula? or i have to change all the formula, hyperlink and vba manually? second, how should i place the workbooks in network folder in a right way? do i need to change any setting at excel file?

## How To Send Email To Different Persons With Different Information / Files

Sep 14, 2013

I am running a small size company, creating several excel works to automate the order/ shipment / invoice work. Try very hard to know how send invoice / AR summary email to different customers.

## Summing Data Over Multiple Sheets According To Persons Name

Jan 16, 2012

formula that will find "John Doe" through sheets 2-10 and check the Win column for a "w"..if a W exists, Sheet 1 will calculate numericaly how many times it shows progressively, but only for his name and not others.

Sheet 1 B7=John Doe
C7 = total W's though sheets 1-10 for John Doe
Sheet 1
B7 C7
John Doe total W's

[Code]...

This is a alteration to an existing question I asked 2 Days ago, I redesigned a new spread sheet to start over with a new approach to it.

## Five Digit Numbers That Represent A Persons Code

Dec 1, 2009

In one column (f5:F100) I have five digit numbers that represent a persons code. I have five groups of codes, they are 10000-15000, 16000-19000, 20000-25000, 26000-29000, and 30000-40000. In another column (Column H) I have an "X" saying the are qualified in something.

I am looking for how to write a statement that says answers this question:

How many people are qualified in the codes between 10000-15000.

Ultimately I am going to take this number and divide it by the total number of people in the group to get a percetage of qualified people in the group.

## Calculate Persons Age Based On Current Date

Jan 22, 2008

how do you calculate someone's age nearest today

## Excel 2013 :: Average Readings Per Day

Jul 2, 2014

Working in Excel 2013......... I take 3 readings per day, I would like to see the daily average per day

In cases where the data is blank or "0", just average the 2 readings.

Sample Data

Date Speed

6/1/20142560
6/1/20142550
6/1/20142558
6/2/20142554
6/2/20142537
6/2/20142532

[Code] ..........

## Excel 2013 :: No Column Headers?

Nov 28, 2013

My and a work college needed to combine our separate excel worksheets into a single document.

Office 2013 didn't have a function to "import sheet from file" so we used open office to import my .xlsx worksheets.

After we finished importing we exported the final workbook as .xls (so I could open it).

After opening the workbook on my pc (excel 2013) i notice some of the sheets no longer have column headers, but the row headings still exists. (No ABCD, only 1234)

Also I am unable to use features such as "Freeze Pane"

I suspect this was caused by importing and exporting through open office?

## Excel 2013 :: Save Workbook To PDF And Name By The Value In A3?

Dec 18, 2013

Tried a macro with no luck, I want to save workbook to PDF and have it named by the value in A3. I want the Dialog box to pop up so I pick the folder?