# Excel Help

Hi, I have a table with a colum full of numbers, these are how many days it has taken for something to be completed (these range from 39 to 115). What i am trying to do is find out what percentage were completed in under a certain amount of days (56 days to be precise). And display the percentage at the bottom of the list in a 'TOTAL's' box.

Anyone have any ideas how its done?

Anyone have any ideas how its done?

I would suggest to use COUNTIF to do a count of those <=56 days then divide by the total*100 %

E.g. =COUNTIF(A2:A70,"<=56") (replace cell range with the one you have in the spreadsheet)

=(COUNTIF(start:end,"<=56")/COUNT(start:end))*100 and format to percentage.

Something like that should work, off the top of my head.

A

In one cell type in =COUNT(A1:A13) this gives the number of cells with a figure in them. You could even type =COUNT(A1:A50), still the answer at this stage will be 13

B

Multiple this cell value by the ‘normal’ duration, say 20 days, gives the expected total if all were done exactly as per normal, here 13x20 giving 260

C

In a cell, add the totals of cells A1-whatever, using =SUM(A1:A50)

Total days now equals number A x C

%, then equals 100 x (A x C) / B

I am using A2 to A11 as your column data

Create a new cell that counts all the cells where value is <56

=COUNTIF(A2:A11,"<56") [lets say its = 4]

Then count the number of cells in the range

=COUNT(A2:A11) [that would be 10]

Then divide the 1st figure by the 2nd to get the percentage

so.... 4/10 = 0.4 [40%]

edit: dragonchris beat me to it, their example does the same, only it does it in the one calculation.[cell]

Edited by:"BurntEffigy" 18th Jan 2011eg.

=IF(A1<=56, 1, 0) --- A1 would be the cell with the number of days in it...

Copy this formula down the column and all values that have '1' are less than 56 days.

In a new cell type in:

=SUM(your whole IF column)/Total amount of samples*100

Thats how I would do it, but i'm sure theres an easier way...

Edited by:"krazie2004" 18th Jan 2011=COUNTIF(A1:A10,"<56")/COUNTA(A1:A10) change cell ranges to suit your data.

format the cell to percentage.

Edited by:"greg_68" 18th Jan 2011Original Poster

Original Poster

just don't include the cell containing the total in your calculation

use a2:a11, not a:a (thats an example if your total is in a12)

edit: what I normally do is have the Total displayed at the top of the list (and use freeze panes), and then no formulas needs to be changed when you add data to the bottom.

Edited by:"BurntEffigy" 18th Jan 2011Original Poster

My column is 'I', at the top (cell I1) it has the name of the column 'Days Elapsed', then from I2 down (to I67 for now), it has figures for how many days have elapsed and I need to work out how many were completed in 56 days or less and show the percentage of these. However, i'm going to continue adding to the 'I' column so currently my formula is '=COUNTIF(I2:I67,"<56")/COUNTA(I2:I67)' and the cell is changed to 'Percentage'. I need this total to be in the 'I' column (preferably at the bottom) but if i change my formula to '=COUNTIF(I:I,"<56")/COUNTA(I:I)' I get an error saying it is creating a circular reference and it doesnt work.

So if I add data to my table, then i'd have to keep changing my formula to include the new cells being used.

Edited by:"davidn84" 18th Jan 2011Other than that do as above, put your 'total' in I2 and you data I3:1000.

Original Poster

Ideally it needs to be in the same column as the other columns have data in and it may get confused with the totals of that column.

I'd prefer it to be at the bottom but I've put it at the top changed the formula to I3-I1000 and its worked so happy with that...unless anyone else has another idea so i could keep it at the bottom?

(Have your total at, say, I2000 - and hide the empty cells [I68:I1999] until you need them)

your last post says 56 days or less

use <56 for under 56 days

or <=56 for 56 days or less

Original Poster

This is what i'm wondering...how can i add a total underneath the data in the column where i can still add to the column and the formula for the total doesnt need changing everytime.

Edited by:"greg_68" 18th Jan 2011Original Poster

Thats worked fine!! I've put my total box at cell 70, and ran the formula from I2 - I69, and if i insert a row at the end of the current data, the formula is changing and its working fine!

Thank you muchly!!

What the F. Totally wrong. Do you work for the inland revenue by any chance, Are you the one that caused all the tax discrepencies last year oO

Edited by:"trog0" 19th Jan 2011