Excel Help

[Vagrant]

I would like to know if anyone could help me with something in excel : i want to add some numbers from a1-a10 and from b1-b10...etc. Now i would like in another spread sheet to add how much of % of x numbers apear the most but to spear in a percentage, if you dont understand give me a whisper and i will try and explain better .

Also if a programmer is looking, is it possible to do this in C++ if you manually add the numbers maybe ?

 

[Robert]

I can help you but need you to be more clear...what do you want sheet 2 to look like?

 

[Vagrant]

ok well so a1-a10,b1-b10 numbers 1,2...10 and in sheet 2 you would have : A1 = 1 B1 = X% from sheet 1 on how many times number 1 has apeared in, for example 1 is in sheet1 from a1 to a10, 9 times that means that in sheet 2 you will have 90% because it apeared 9/10. Well i will probably have much larger number like from 1 to 80 but i just need to know base formula because i couldnt get it to work last time i tried it and i though i would ask some help here

 

[Pottsy]

Well to display as a solid number, you can use this Formula...

=COUNTIF(A1:A10,"1")

This would count many one 1s have occurred in cells A1 to A10

This could also be used with conditions and such...

=COUNTIF(A1:A10,">5")
Would count values greater than a value of 5, note the quotations.

An easy way around your problem though, using the above equation....You can do this...

The Cell you are using this equation on, you can set to display as a percentage. Problem is, this shows it at sets of 100%...For example, if it picked up 8 1s, it would show 800%.

To get around this, divide the equation by the amount of cells you have in the equation...as below, there are 20 cells selected, so you divide by 20
=COUNTIF(A1:A20,"1")/20

And voila, you have a % out of the total number of cells listed, to show how many 1s you have out of 100% lol...Get it? :P

 

[Ardbeg]

Assuming just a value of 1 or blank in the cells.
A1:A20 is the row range where sum adds, change as required.
12 = row count, again, change as required.

Simply enter the following in any cell and the result will be the % of 1s in your row count.

=SUM(A1:A20)/12*100

 

[Vagrant]

Assuming just a value of 1 or blank in the cells.
A1:A20 is the row range where sum adds, change as required.
12 = row count, again, change as required.

Simply enter the following in any cell and the result will be the % of 1s in your row count.

=SUM(A1:A20)/12*100)

I really dont understand, i dont want to calculate the sum, i want to calculate the percentage of X row's the number apeared in, so if i make 10 rows ye ? and number one apeared in 8 out of 10 rows that means when i put formula in i have a percentage of 80%

Well to display as a solid number, you can use this Formula...

=COUNTIF(A1:A10,"1")

This would count many one 1s have occurred in cells A1 to A10

This could also be used with conditions and such...

=COUNTIF(A1:A10,">5")
Would count values greater than a value of 5, note the quotations.

An easy way around your problem though, using the above equation....You can do this...

The Cell you are using this equation on, you can set to display as a percentage. Problem is, this shows it at sets of 100%...For example, if it picked up 8 1s, it would show 800%.

To get around this, divide the equation by the amount of cells you have in the equation...as below, there are 20 cells selected, so you divide by 20
=COUNTIF(A1:A20,"1")/20

And voila, you have a % out of the total number of cells listed, to show how many 1s you have out of 100% lol...Get it? :P

Well the problem with your formula if its only 1(i mean number one apeared one time in the row) and you divide by 20 then its 5%, that is not true, its actually 100% , if number one will not apear in cell B1-B10, then the percentage would change to 50%. I dont think it works with your formula

 

[Ardbeg]

Five rows with 1 in = a count of five rows.
Add 5 1s and you get 5.

That's why I say my formula assumes your cell value will always be a 1. If it is, it will work, if its greater than one then it will give incorrect results.
Try it !

 

[Babyhack]

=countif(a1:a10,"1")/count(a1:a10)*100

bh

 

[Ardbeg]

=countif(a1:a10,"1")/count(a1:a10)*100

bh

Even better.
Thanks bh

 

[Vagrant]

=countif(a1:a10,"1")/count(a1:a10)*100

bh

i only have number 1 once and it says 250%

 

[Babyhack]

i only have number 1 once and it says 250%

Odd as I tested with work data and it worked perfect

could you upload your excel file or even a screenshot with the data/numbers?

From reading you want it across different sheets, this is easy enough to do but I would need some sample data

BH

 

[Robert]

Did you remember to lock your range? Otherwise when you drag the formula down you will get distorted results.

 

[Vagrant]

Did you remember to lock your range? Otherwise when you drag the formula down you will get distorted results.

Yea i didnt lock with (), works now but not like it should because i have 2 colums, and in the first colum number 1 apears and in colum 2 it doesnt so it should say 50%, if i add more ''1'' it will add 200%,300% etc. See in picture, formula is : =COUNTIF(Sheet1!B1:U2,"1")/COUNT((Sheet1!B1:U2)*100)

 

[Babyhack]

Yea i didnt lock with (), works now but not like it should because i have 2 colums, and in the first colum number 1 apears and in colum 2 it doesnt so it should say 50%, if i add more ''1'' it will add 200%,300% etc. See in picture, formula is : =COUNTIF(Sheet1!B1:U2,"1")/COUNT((Sheet1!B1:U2)*100)

=COUNTIF(Sheet1!B1:U2,"1")/COUNT((Sheet1!B1:U2)*100)

Take the ( in red out, thats whats messing it up

it should be =COUNTIF(Sheet1!B1:U2,"1")/COUNT(Sheet1!B1:U2)*100

The whole result needs to be *100 not just the results of the count (second part)
for example your formula (with the brackets) is doing (result)/(count*100) ie if result is 1 and count is 10 you are getting 1/1000 = 0.001%
where as without the brackets is (result)/(count)*100 so (1/10)*100 = 10%

I dont have access to excel right now so am not able to confirm but am 99% sure the above is correct


BH

 

[Ardbeg]

If you're using OpenOffice Calc rather than excel, the comma separator in COUNTIF needs to be replaced with a semicolon.
Apart from that difference, the formula works fine.

 

[Pottsy]

I really dont understand, i dont want to calculate the sum, i want to calculate the percentage of X row's the number apeared in, so if i make 10 rows ye ? and number one apeared in 8 out of 10 rows that means when i put formula in i have a percentage of 80%


Well the problem with your formula if its only 1(i mean number one apeared one time in the row) and you divide by 20 then its 5%, that is not true, its actually 100% , if number one will not apear in cell B1-B10, then the percentage would change to 50%. I dont think it works with your formula

So you want to show the % of 1s selected, out of the total number of 1s in the sheet? As oppose to the total of numbers selected all together.

Then my formula is right, you just explained yourself wrong...don't have time to find the proper formula right now though lol

 

[Vagrant]

So you want to show the % of 1s selected, out of the total number of 1s in the sheet? As oppose to the total of numbers selected all together.

Then my formula is right, you just explained yourself wrong...don't have time to find the proper formula right now though lol

dont make me come to england....

=COUNTIF(Sheet1!B1:U2,"1")/COUNT((Sheet1!B1:U2)*100)

Take the ( in red out, thats whats messing it up

it should be =COUNTIF(Sheet1!B1:U2,"1")/COUNT(Sheet1!B1:U2)*100

The whole result needs to be *100 not just the results of the count (second part)
for example your formula (with the brackets) is doing (result)/(count*100) ie if result is 1 and count is 10 you are getting 1/1000 = 0.001%
where as without the brackets is (result)/(count)*100 so (1/10)*100 = 10%

I dont have access to excel right now so am not able to confirm but am 99% sure the above is correct


BH

I copied and paste your formula and it says 250%

 

[Robert]

=(COUNTIF(Sheet1!B$1:U$2,"1")/COUNT(Sheet1!B$1:U$2))*100

 

[Babyhack]

dont make me come to england....



I copied and paste your formula and it says 250%

Just tested it and its working as it should

If you have the results cell formatted as % then you need to drop the *100 from the formula



BH

 

[Vagrant]

thanks for the help everyone