[RESOLVED] Excel Formula Needed

[MartinLiss]

In a column that contains numbers with values from 1 to 9, I'd like a formula that will calculate the percent of times that the number 1 shows up.

[dglienna]

I always do things the easy way. If you ever wanted to use the number 2, then you'd have to recode all the statements to do it, which doesn't make sense.
There might be an easier way, but I would just use a cell that is a flag to whether your number is there or not, and then count the numbers, and the flags.

I did this, and formatted it as a percentage with 0 decimal places

[MartinLiss]

Thanks but I don't want to add any extra columns or rows (even if I were to hidethem).

[RobDog888]

How about just adding a filter? Then you could just count the rows. It would be dynamic too.

[MartinLiss]

Sounds good but I'd have no idea how to do that. (I'll pick this up again tomorrow if someone responds).

[RobDog888]

Place the active cell in the column and one row above the row data to be filtered.
Data > Filter > Autofilter. Then click on the combo box that appears and you get a choice of many options: filter by a particular unique
content, Top 10, All, Custom, Sort Asc, Sor Desc, etc.

[dglienna]

I thought you wanted to do it programmatically. Adding a calculation column isn't a bad thing unless you are using all of them

[visualAd]

You should use the COUNTIF() function for this. You can get it to count only the values which meet a certain criteria then use the COUNT() function to find the total number of cells in the range.

1. Highlight the range of cells you want to include in your formula and go to Insert->Name call the range numbers.
2. Find the cell where you want to calculate the percentage and enter the following formula.
   

   =COUNTIF(numbers,1)/COUNT(numbers)

3. Format the cell as a percentage type by right clicking and clicking Format Cells

Have a look at the attachment to see the result.

[MartinLiss]

You should use the COUNTIF() function for this. You can get it to count only the values which meet a certain criteria then use the COUNT() function to find the total number of cells in the range.

1. Highlight the range of cells you want to include in your formula and go to Insert->Name call the range numbers.
2. Find the cell where you want to calculate the percentage and enter the following formula.
   

   =COUNTIF(numbers,1)/COUNT(numbers)

3. Format the cell as a percentage type by right clicking and clicking Format Cells

Have a look at the attachment to see the result.

How do I define "numbers"?

[visualAd]

Its just an easier way of specifying a range of cells. Highlight the cells you want to include in the range and then goto Name->Define in the Insert menu. You should then get a dialog box which enables you to give the range a name.

[MartinLiss]

Thanks!