# Seeking To Average Numbers In Single Column Broken Into Increments Of 5

Sep 12, 2008
I have a column of over 500 replies to a survey. I want to create a pie chart showing the average ages of the respondee to that that question, broken down into increments of 5 years (this was the question -- how old are you) and reported as a percentage.

View 9 Replies
ADVERTISEMENT
Jun 14, 2013

E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).

E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.

I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.

View 14 Replies
View Related
Jan 12, 2007

I'm looking for an answer about how I can show the numbers underlying a

nested IF function. The formula works fine, but I need to be able to show

how I arrived at the numbers that I did. Here is an example of one of the formulas:

=IF(B9=1,(B4*Data!G5*Data!F5),IF(Calculations!B10=2,(Calculations!B5*Data!G5*Data!F5),IF(Calculations!B11=3,(Calculations!B6*Data!G5*Data!F5),0)))

Instead of the reference to the cell, I would like the formula that the cell represents. I know that I could go through individually and pull each piece apart with control F9, but since there are numerous cells and because the data changes every time I create a new budget, it would help if I could have some way to display it all with one action.

View 9 Replies
View Related
May 13, 2008

I'm trying to write some VBA code that can vary an undefined number of variables from a certain value to a certain value and by a certain increment. I know I can do this using For, from to step function but I could potentially have as many as 60 variables that need to be permuted and as little as 1.

For example:

Permute variable 1 from 2 to 6 by 2

Permute variable 2 from 6 to 8 by 1

should output

2 6

2 7

2 8

4 6

4 7

4 8

6 6

6 7

6 8

how to make this code efficient instead of writing 60 for loops? that could either be used or not?

View 4 Replies
View Related
Jan 31, 2013

I have taken over this spreadsheet for my work, and it is basically a statement in excel. What I want to do is find a list of invoice numbers in column B populated from a remittance, and then replace column F to say a specific thing depending on check number and date paying for that invoice. So if a check printed today I would have it replace column f to say paid 1/31/13 check # xxxxx. Currently I am searching for each invoice indivudually and then replacing with check number and date. There are about 200 invoices per month that I deal with, and it is a big waste of time!

For example from this

invoice #1 / reconciled (DATE)

invoice #2 / reconciled (DATE)

To this:

invoice #1 / paid (DATE) check # (xxxxx)

invoice #2 / paid (DATE) check # (xxxxx)

View 2 Replies
View Related
Dec 22, 2008

Column B values are the average of the most recent five values in column A.

The formula in Cell C1 is “=B7” so that the value in C1 is 1.17. My question is: When I place a value in cell A8, which will result in the average of the five numbers in column A4-A8 being placed in cell B8 how do I make the value in C1 change to be the value which is in cell B8 rather than the value which is in cell B7?

Note: I just want to be able to pick up the most recent average value – the

method whereby it is done is of no consequence. However I cannot think

of an alternative. Example:.....

View 5 Replies
View Related
Jul 20, 2006

I am trying to average a column of numbers. I want to only average the cells

that contain a number greater than 0.

View 10 Replies
View Related
Sep 26, 2006

how to create a column chart with a broken y-axis? And if this is possible, is it also possible to have 2 gaps in the y-axis. I have several countries in which i gathered information about their (expected) population sizes over time (2003 - 2011). For each country i want to make a chart in which their population size is shown over time. In order to compare these countries i want these charts all having the same y-axis scale (which expresses the population size). To be able to do this i want to put some gaps in the y-axis. I think i need 2 gaps because i can divide the countries in "low population" countries (2-3 million), "moderate population" countries (7-14 million) and "high population" countries (> 30 million). I attached a sample file to make things more clear! I hope somebody knows more how to do it!

View 2 Replies
View Related
Jun 4, 2014

Daily Result

14

10

16

10

5

15

8

12

15

9

I have the formula working for the average of the 4 lowest of all, using "AVERAGE" and "SMALL"; need for average of 4 lowest in most recent 7

View 6 Replies
View Related
Mar 27, 2009

I wanted to know how I can calculate the average gain and average loss for data that is inputted into one column? For example;

Column A

$12,000

-$5,000

$6,500

-$1,000

$8,000

-$4,000

Average Gain ??

Average Loss ??

View 4 Replies
View Related
Jul 2, 2007

How to copy an entire column having numbers and words on every single cell into another column but only copying numbers, not words.

View 9 Replies
View Related
May 4, 2008

I have to take a range of phone numbers that I have to port from another carrier and manually type them out or copy into another speadsheet, removing duplicate numbers. For example, I have the following numbers:

55532821055553282108

78132821235553282123

55532821625553282163

The results on the next spreadsheet need to be:

5553282105

5553282106

5553282107

5553282108

7813282123

5553282162

5553282163

I am looking for a way to automate this process.

View 8 Replies
View Related
Feb 13, 2012

I am using excell 2007 and 2010

I need to find the average numbers on sheet 1 column D. Column D contains numbers as well as Blank cells. The info will be presented on sheet 2 in a monthly view. In other words i need to ssearch awithin a date range and determin the average of those numbers. This works on some months but return a DIV/0 erro most of the time.

=AVERAGEIFS('2N Stats'!$I:$I,'2N Stats'!$E:$E,">=2/1/2011",'2N Stats'!$E:$E,"

View 2 Replies
View Related
Sep 8, 2012

I'm new to Excel (4th day in class) and I can't find the answer to what I'm looking for on the net. Part of the problem is that I don't know what to call it, so I have a hard time looking it up.

Very simply put, let's say there are examples numbers 1,2,3,4 put in cells A1 to A4 (vertical) and more example number 50,60,70,80 are put in cells A6 to D6 (horizontal)

What one formula can be written in B1 that I can copy down to B4 that will make B1=A1+A6, and B2=A2+B6, and B3=A3+C6, and B4=A4+D6

View 1 Replies
View Related
Jan 27, 2012

I have a spreadsheet wtih a number of rows that contain answers to different criertia in each column.

The row may therefore have cells that either have numbers or letters.

Column A will be the title of the row.

I want to write a function to find the average value of the numbers in any row with title 'x'.

So it would look down column A, and look for those called 'x', and then average all the numbers across all those rows.

I have tried to use =averageif, but I think the fact there are letters in the cells being assessed (which I just want to be ignored) creates an error.

View 9 Replies
View Related
Mar 2, 2009

I've got a "Date" Column which has every day of every month, and next to that several columns with different types of data to correspond to a particular date.

To simplify this (because there are TONS of dates), I've made another "Date" column that displays only every 5th day. Now what I'd like to do of course is average the corresponding data over five days.

I don't know how to write a single formula that I can copy down that will do this.

I've attached a sample spreadsheet with only one month's data, the stuff I need a formula / code for is in red

View 2 Replies
View Related
Mar 21, 2008

I have a text file containing internet explorer browser history. The file has data in the following format (in Excel all data is in 1 column): ...

View 9 Replies
View Related
May 31, 2013

I am trying to extract times from a work schedule to automatically generate a 'time in' / 'time out' chart. Been trying to use the lookup formula amongst others with little success.

See example template attached...Example Sheet.xlsx

View 9 Replies
View Related
Jul 13, 2009

I'm looking for the reason why the following use of the INDEX function returns a cell address [ie the formula evaluates SUM($C$2:C3)]-

View 5 Replies
View Related
Jun 4, 2014

Say employee John Doe has a salary X. Giving him a salary raise Y (euro/dollar) induces a pension premium to be paid by the employer, on that amount.

The premium is (fixed) 17% of the part above a (fixed) treshold value of 3.750. On the part of the raise below the 3.750, the pension premium is (fixed) 4,35%.

E.g. having a salary of 3.600, with a raise of 400.

4,35% of (3.750 - 3.600 = 150) equals 6,525

And 17% of the remaining 250 equals 42,500

So the total premium is 49,025

But, I want to give a raise with a total cost of no more than 400. So, I use goal seek to find a raise that, together with te pension premiums equals 400.

In the example, with 3.600 as a salary to start with, that raise would be 358,10 Because the part below 3.750 stays the same and induces a premium of 4,35% of 150, being 6,525 and the remaining part is 208,10 at 17% is 35,377.

Proof: 358,10 + 6,525 + 35,377 = 400

The question is: is there a possibility to have a formula that calculates the (e.g.) 358,10 based on a salary to start with (e.g. 3.600), an aimed total cost (e.g. 400) and the fixed constants 3.750, 17% and 4,35%?

View 3 Replies
View Related
Feb 1, 2007

I need to determine federal tax withholding from gross pay.

If gross amount (for S-0 = single no dependents) is

Over.................But NOT Over............Then Subtract.........And multiply

$0.00...............$195.00.....................$5 1.......................10%

$195.00............$645.00.....................$99 .......................15%

$645.00............$1,482.00..................$351 ......................20%

$1,482.00..........$3,131.00..................$447 ......................25%

$3,131.00..........$6,763.00..................$849 .....................30%

If gross is $400 then what are the total taxes taken out? Could someone please provide a formula?

I tried this one: =IF(M10

View 9 Replies
View Related
Mar 18, 2014

I have thousands of data entries (each in their own row, all in one column) and need to separate the basic info. In the example below, say you want to isolate the Town names by entering a formula that can produce a TRUE or FALSE result. Is there a way to use the FIND function and a pool of terms to isolate cells that contain excess data (i.e.- produce a FALSE result for cells containing any words from a term pool like : "Police, Park, Power, Water, Community, Bakery, Grocery, Field, Garden, Post Office, School" etc... ) ?

Pic of sample spreadsheet with desired result:

Screen Shot 2014-03-18 at 3.14.09 AM.png

example as text:

A2 = Springfield

A3 = Springfield Police Department

A4 = Springfield Community College

A5 = Springfield Park Of Recreation

A6 = Des Moines

A7 = Des Moines Power & Water Company

A8 = Des Moines Sunrise Bakery

A9 = Des Moines Post Office

A10 = Des Moines Grocery Store

A11 = Lancaster

A12 = Lancaster Baseball Field

A13 = Lancaster Botanical Gardens

View 8 Replies
View Related
Jun 18, 2013

I have repetitive task of copying and pasting. Macro to sort some data.

I have time values (sorted in order) in Column A and a value in Column B.

Sheet1 Â AB1TimeValue

200:00:002300:00:002400:00:001500:00:002601:00:004701:00:002802:00:001902:00:0021002:00:0031102:00:0041202:00:0011302:00:002

I need separate the time each hour of data into columns. So the above would become

Sheet1 Â ABCDEF1TimeValueTimeValueTimeValue

200:00:00201:00:00402:00:001300:00:00201:00:00202:00:002400:00:001Â Â 02:00:003500:00:002Â Â 02:00:0046Â Â Â Â 02:00:0017Â Â Â Â 02:00:002

There are a different number of rows of data for each hour and sometimes there might not be any data for a specific hour.

View 4 Replies
View Related
Apr 29, 2014

I have created an online survey, and people choose ten words (skills) out of 24 possible. Please see screenshot. I would like a formula that does this in layman's terms: "If I see the word "Cooperation" in the source cell, then I'll put "03b" into the target cell; but if I see "Managing" in the source cell instead, I'll put "21a" in the target cell, etc."

I've tried a few IF / Then statements, with no success. Screenshot shows the source cell upper right, and the ten target cells below left with two rows of sample results. I'm guessing the same formula can be in each of the 10 target cells.

View 8 Replies
View Related
Mar 3, 2012

With exception to Duplicate numbers on a single row, or column. I am hoping I can check an entire row against other rows, to see idf the same number are repeated.

Unlike the Lotto, this is about Safety boxes, where I have sequential numbering such as the following:

Row2:5,11,15,16,19,35

Row16: 2,8,12,17,18,33,

Row102: 2,6,8,14,22,44

Row645: 2,6,8,14,22,44

Notice rows 102 and 645

I can't believe all of our staff missed the duplicate lines 102 and 645 of duplicate Although these row numbers were rows 2, 16, 102, & 245

View 1 Replies
View Related
Jan 5, 2008

im using a formula to count how many time a customer has payed their bill using COUNTIF.

along with that i would like to use some kind of reference or a secont formula like

=COUNTIF(range1:range2,"Jean K") + another formula, so it will only calculate the amount Jean K has paid, and won;t count everyones elses paymets. i just need a little help to make this happen.

and also included a sample of my sheet in a ScreenShot.

View 3 Replies
View Related
Aug 18, 2009

Field 1 input: Beginning Date (e.g 7/17/09)

Field 2 input: End Date (e.g. 7/23/09)

Need: Average costs on and between these given dates

From table with this information:

Date Market two

7/16/2009 80

7/17/2009 80

7/20/2009 82

7/21/2009 84

7/22/2009 82

7/23/2009 82

7/24/2009 82

7/27/2009 82

I need Excel to average Market two 7/17 to 7/23 (80, 82, 84, 82, 82) based on the dates I give it.

If I change the date to 7/20 to 7/24, I then need excel to average (82, 84, 82, 82, 82)

How can I get Excel to average based on different dates? Or basically, how do I get it to pull rows between 2 inputs?

View 4 Replies
View Related
Nov 27, 2005

I m making a worksheet for our shooting results. I need the average of the

top 3 results from collum E6 to W6 and top 3 reults from Y6 to AJ6. Can

View 14 Replies
View Related
Mar 31, 2009

What is the function to add numbers in a single cell to show only a single number. I know it can be done but do not know the quick function to do it.

View 10 Replies
View Related
Aug 23, 2012

I have a string of numbers like this:

378 398 308 978 397 328 078 307 908 278

And this code works fine except for I get a Type 13 Mismatch error on this line of code:

Code:

x = Application.Transpose(Split(my_cell.Value, " "))

Here is the entire sub:

Code:

Public Sub split_down()

Dim x As Variant

Dim my_range As Range

[Code]....

How can I adjust this for several lines of numbers like this?

378 398 308 978 397 328 078 307 908 278

390 327 928 678 392 907 208 320 927 178

608 394 207 578 391 920 108 607 395 324

508 321 168 107 325 924 568 507 341 921

167 345 925 518 567 351 941 160 945 517

560 951 241 245 510 251 516 451 388 338

788 988 337 088 778 339 377 288 330 998

And what is going on with this error. The first number string above should be placed in cell A1 when trying this code.

View 4 Replies
View Related