Excel Help - HotUKDeals
Get the HUKD app free at Google Play

#### Search Error

An error occurred when searching, please try again!

# Excel Help

5y, 10m agoPosted 5 years, 10 months ago
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?
5y, 10m agoPosted 5 years, 10 months ago
Options

(18)
2 Likes #1
Probably a couple of ways you could do this.

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.
#2
I have a column of numbers, say in cells A1 – A13

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
#3
What I would do is...
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 on Jan 18, 2011 10:14
#4
In a new column, first use the IF function to sort out exactly how many are under 56 days.

eg.

=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 on Jan 18, 2011 10:18
#5
In the total cell

=COUNTIF(A1:A10,"<56")/COUNTA(A1:A10) change cell ranges to suit your data.

format the cell to percentage.

Edited By: greg_68 on Jan 18, 2011 10:23
#6
Thanks for all your help...i've managed to do it!
#7
Sorry to bother again...i've created lists in the table, is it possible to include everything in the list? If i do the whole column, it fails because the answer is in the column and it creates a circlular thing. If i can do the list then the answer will change when i put new entries into the table, without me having to change the formula.
#8
Not really sure on the exact problem?
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 on Jan 18, 2011 11:20
#9
BurntEffigy
Not really sure on the exact problem?just don't include the cell containing the total in your calculationuse 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.

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 on Jan 18, 2011 11:29
#10
Can you not have the 'total' in another column, then expand your ranges to say I2:1000?

Other than that do as above, put your 'total' in I2 and you data I3:1000.
#11
greg_68
Can you not have the 'total' in another column, then expand your ranges to say I2:1000?Other than that do as above, put your 'total' in I2 and you data I3:1000.

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?
#12
The only other option that springs to mind is to use the Hide Cells - although that can get messy.
(Have your total at, say, I2000 - and hide the empty cells [I68:I1999] until you need them)
#13
If your data is in 1:100, where is your 'total' boxgoing to be placed. If it is in 101, how do you put new data in, do you insert rows?
#14
also.. the OP says you wanted to know number completed in under 56 days
your last post says 56 days or less

use <56 for under 56 days
or <=56 for 56 days or less
#15
greg_68
If your data is in 1:100, where is your 'total' boxgoing to be placed. If it is in 101, how do you put new data in, do you insert rows?

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.
#16
If you insert rows the formula will auto change, that's why I asked how you inserted extra data. just wondered whether inserting rows would have an effect on any other columns data?

Edited By: greg_68 on Jan 18, 2011 12:03
#17
greg_68
If you insert rows the formula will auto change, that's why I asked how you inserted extra data. just wondered whether inserting rows would have an effect on any other columns data?

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!!
#18
wbmkk
I have a column of numbers, say in cells A1 – A13AIn 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 13BMultiple 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 260CIn 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

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 on Jan 19, 2011 05:32