Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed...
I have a row of numbers from 1 to 26 in cells A1 to Z1 representing numbers
'chosen' at random by 500 unique people. I have another row of numbers representing the 'frequency' that the aforementioned number was chosen in cells A2 to Z2. Example: 1 2 3 4 5 6 7 8 9 10 11 ... 10 4 16 9 23 20 61 13 18 43 17 ... Is there a formula that I could use to calculate the percentage (0%-100%) that each number was chosen? Thanx. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed...
Assuming A2 to Z2 are the frequencies:
in cell A3 =A2/SUM($A$2:$Z$2) Copy the formula to the rest of the columns. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed...
On Wed, 16 Nov 2005 11:05:45 -0800, Steven Sinclair
wrote: I have a row of numbers from 1 to 26 in cells A1 to Z1 representing numbers 'chosen' at random by 500 unique people. I have another row of numbers representing the 'frequency' that the aforementioned number was chosen in cells A2 to Z2. Example: 1 2 3 4 5 6 7 8 9 10 11 ... 10 4 16 9 23 20 61 13 18 43 17 ... Is there a formula that I could use to calculate the percentage (0%-100%) that each number was chosen? Thanx. Shouldn't that just be =Frequency_of_num_choices/500 --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed...
Sum all your frequencies. If they exceed 500 you have a data integrity issue.
"Steven Sinclair" wrote: I thought so too, but when I put that formula in, I end up with values over 100%. Thanx. "Duke Carey" wrote: wouldn't it just be the frequency divided by 500? "Steven Sinclair" wrote: I have a row of numbers from 1 to 26 in cells A1 to Z1 representing numbers 'chosen' at random by 500 unique people. I have another row of numbers representing the 'frequency' that the aforementioned number was chosen in cells A2 to Z2. Example: 1 2 3 4 5 6 7 8 9 10 11 ... 10 4 16 9 23 20 61 13 18 43 17 ... Is there a formula that I could use to calculate the percentage (0%-100%) that each number was chosen? Thanx. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed...
I thought so too, but when I put that formula in, I end up with values over
100%. Thanx. "Duke Carey" wrote: wouldn't it just be the frequency divided by 500? "Steven Sinclair" wrote: I have a row of numbers from 1 to 26 in cells A1 to Z1 representing numbers 'chosen' at random by 500 unique people. I have another row of numbers representing the 'frequency' that the aforementioned number was chosen in cells A2 to Z2. Example: 1 2 3 4 5 6 7 8 9 10 11 ... 10 4 16 9 23 20 61 13 18 43 17 ... Is there a formula that I could use to calculate the percentage (0%-100%) that each number was chosen? Thanx. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed...
In cell A3 you could put the formula:
=A2/sum($A$2:$Z$2) Then just copy A3 to all cells from B3 to Z3. The $ above will prevent Excel from automatically incrementing those references as the formula is copied over. And by using the SUM function rather than 500, you can get an accurate percentage regardless of how many total results you have. "Steven Sinclair" wrote: I have a row of numbers from 1 to 26 in cells A1 to Z1 representing numbers 'chosen' at random by 500 unique people. I have another row of numbers representing the 'frequency' that the aforementioned number was chosen in cells A2 to Z2. Example: 1 2 3 4 5 6 7 8 9 10 11 ... 10 4 16 9 23 20 61 13 18 43 17 ... Is there a formula that I could use to calculate the percentage (0%-100%) that each number was chosen? Thanx. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed...
wouldn't it just be the frequency divided by 500?
"Steven Sinclair" wrote: I have a row of numbers from 1 to 26 in cells A1 to Z1 representing numbers 'chosen' at random by 500 unique people. I have another row of numbers representing the 'frequency' that the aforementioned number was chosen in cells A2 to Z2. Example: 1 2 3 4 5 6 7 8 9 10 11 ... 10 4 16 9 23 20 61 13 18 43 17 ... Is there a formula that I could use to calculate the percentage (0%-100%) that each number was chosen? Thanx. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed...
Unfortunately, when I use that formula, all I get in the A3 to Z3 cells are
numbers like [ 0.113065327 0.045226131 0.118090452 0.170854271 0.108040201 0.005025126 0.06281407 0.047738693 0.211055276 0.118090452 ....]. Assuming, based on those numbers that I need to format the cells with a PERCENT format, the numbers all add up wrong...I end up with numbers higher than 100%. Any other ideas? Thanx. "Elkar" wrote: In cell A3 you could put the formula: =A2/sum($A$2:$Z$2) Then just copy A3 to all cells from B3 to Z3. The $ above will prevent Excel from automatically incrementing those references as the formula is copied over. And by using the SUM function rather than 500, you can get an accurate percentage regardless of how many total results you have. "Steven Sinclair" wrote: I have a row of numbers from 1 to 26 in cells A1 to Z1 representing numbers 'chosen' at random by 500 unique people. I have another row of numbers representing the 'frequency' that the aforementioned number was chosen in cells A2 to Z2. Example: 1 2 3 4 5 6 7 8 9 10 11 ... 10 4 16 9 23 20 61 13 18 43 17 ... Is there a formula that I could use to calculate the percentage (0%-100%) that each number was chosen? Thanx. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed...
What you are posting is inconsistent.
If each of your 500 people make one choice, then there are 500 choices made. That has to be the denominator. Unless your 500 people are making more than one choice apiece. Also, in your example, the first entry (under the 1) was a 10; the second entry, under the 2, was a 4. =10/500 is 0.02; not 0.113065327 =4/500 is 0.008; not 0.045226131 So there is clearly something inconsistent between what you've posted and what you are getting for results. If your frequency is in A2:Z2, then in A3 try the formula: =A2/SUM$A$2:$Z$2) and copy/drag across On Wed, 16 Nov 2005 15:45:05 -0800, Steven Sinclair wrote: Unfortunately, when I use that formula, all I get in the A3 to Z3 cells are numbers like [ 0.113065327 0.045226131 0.118090452 0.170854271 0.108040201 0.005025126 0.06281407 0.047738693 0.211055276 0.118090452 ...]. Assuming, based on those numbers that I need to format the cells with a PERCENT format, the numbers all add up wrong...I end up with numbers higher than 100%. Any other ideas? Thanx. "Elkar" wrote: In cell A3 you could put the formula: =A2/sum($A$2:$Z$2) Then just copy A3 to all cells from B3 to Z3. The $ above will prevent Excel from automatically incrementing those references as the formula is copied over. And by using the SUM function rather than 500, you can get an accurate percentage regardless of how many total results you have. "Steven Sinclair" wrote: I have a row of numbers from 1 to 26 in cells A1 to Z1 representing numbers 'chosen' at random by 500 unique people. I have another row of numbers representing the 'frequency' that the aforementioned number was chosen in cells A2 to Z2. Example: 1 2 3 4 5 6 7 8 9 10 11 ... 10 4 16 9 23 20 61 13 18 43 17 ... Is there a formula that I could use to calculate the percentage (0%-100%) that each number was chosen? Thanx. --ron |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed...
I guess I need to explain it better...never really was that good at math.
8^) Looking at all the available functions within Excel, the closest one to what I'm looking for would have to be the RANK function. However, even that one is not quite there. What I need is a function that, based on the numbers chosen and the freqency of each number chosen, I'd like to display the results based on a scale between 1% and 100% instead of based on the percent of how many choices. Does that make sense? Thanx. "Ron Rosenfeld" wrote: What you are posting is inconsistent. If each of your 500 people make one choice, then there are 500 choices made. That has to be the denominator. Unless your 500 people are making more than one choice apiece. Also, in your example, the first entry (under the 1) was a 10; the second entry, under the 2, was a 4. =10/500 is 0.02; not 0.113065327 =4/500 is 0.008; not 0.045226131 So there is clearly something inconsistent between what you've posted and what you are getting for results. If your frequency is in A2:Z2, then in A3 try the formula: =A2/SUM$A$2:$Z$2) and copy/drag across On Wed, 16 Nov 2005 15:45:05 -0800, Steven Sinclair wrote: Unfortunately, when I use that formula, all I get in the A3 to Z3 cells are numbers like [ 0.113065327 0.045226131 0.118090452 0.170854271 0.108040201 0.005025126 0.06281407 0.047738693 0.211055276 0.118090452 ...]. Assuming, based on those numbers that I need to format the cells with a PERCENT format, the numbers all add up wrong...I end up with numbers higher than 100%. Any other ideas? Thanx. "Elkar" wrote: In cell A3 you could put the formula: =A2/sum($A$2:$Z$2) Then just copy A3 to all cells from B3 to Z3. The $ above will prevent Excel from automatically incrementing those references as the formula is copied over. And by using the SUM function rather than 500, you can get an accurate percentage regardless of how many total results you have. "Steven Sinclair" wrote: I have a row of numbers from 1 to 26 in cells A1 to Z1 representing numbers 'chosen' at random by 500 unique people. I have another row of numbers representing the 'frequency' that the aforementioned number was chosen in cells A2 to Z2. Example: 1 2 3 4 5 6 7 8 9 10 11 ... 10 4 16 9 23 20 61 13 18 43 17 ... Is there a formula that I could use to calculate the percentage (0%-100%) that each number was chosen? Thanx. --ron |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed...
On Thu, 17 Nov 2005 07:58:13 -0800, Steven Sinclair
wrote: I guess I need to explain it better...never really was that good at math. 8^) Looking at all the available functions within Excel, the closest one to what I'm looking for would have to be the RANK function. However, even that one is not quite there. What I need is a function that, based on the numbers chosen and the freqency of each number chosen, I'd like to display the results based on a scale between 1% and 100% instead of based on the percent of how many choices. Does that make sense? No. At least not in conjunction with your previous posts. If you have 500 people each making one choice amongst 26 options; and 50 of them choose option 2; then 50/100 = 10% will have chosen option 2. If that's not the kind of result you are looking for, then I'd need to see a better example of what the data looks like, and what the results should look like, than I've seen so far. --ron |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed...
Can I zip and email you the spreadsheet in question?
"Ron Rosenfeld" wrote: On Thu, 17 Nov 2005 07:58:13 -0800, Steven Sinclair wrote: I guess I need to explain it better...never really was that good at math. 8^) Looking at all the available functions within Excel, the closest one to what I'm looking for would have to be the RANK function. However, even that one is not quite there. What I need is a function that, based on the numbers chosen and the freqency of each number chosen, I'd like to display the results based on a scale between 1% and 100% instead of based on the percent of how many choices. Does that make sense? No. At least not in conjunction with your previous posts. If you have 500 people each making one choice amongst 26 options; and 50 of them choose option 2; then 50/100 = 10% will have chosen option 2. If that's not the kind of result you are looking for, then I'd need to see a better example of what the data looks like, and what the results should look like, than I've seen so far. --ron |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed...
OK, but since I'm paranoid, what you'll need to do is reverse the string, make
the obvious substitution, and then throw out the garbage. mocegabrag.enilnodlefnesorTAzyx On Thu, 17 Nov 2005 10:07:30 -0800, Steven Sinclair wrote: Can I zip and email you the spreadsheet in question? "Ron Rosenfeld" wrote: On Thu, 17 Nov 2005 07:58:13 -0800, Steven Sinclair wrote: I guess I need to explain it better...never really was that good at math. 8^) Looking at all the available functions within Excel, the closest one to what I'm looking for would have to be the RANK function. However, even that one is not quite there. What I need is a function that, based on the numbers chosen and the freqency of each number chosen, I'd like to display the results based on a scale between 1% and 100% instead of based on the percent of how many choices. Does that make sense? No. At least not in conjunction with your previous posts. If you have 500 people each making one choice amongst 26 options; and 50 of them choose option 2; then 50/100 = 10% will have chosen option 2. If that's not the kind of result you are looking for, then I'd need to see a better example of what the data looks like, and what the results should look like, than I've seen so far. --ron --ron |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed...
On Thu, 17 Nov 2005 12:22:09 -0500, Ron Rosenfeld
wrote: On Thu, 17 Nov 2005 07:58:13 -0800, Steven Sinclair wrote: I guess I need to explain it better...never really was that good at math. 8^) Looking at all the available functions within Excel, the closest one to what I'm looking for would have to be the RANK function. However, even that one is not quite there. What I need is a function that, based on the numbers chosen and the freqency of each number chosen, I'd like to display the results based on a scale between 1% and 100% instead of based on the percent of how many choices. Does that make sense? No. At least not in conjunction with your previous posts. If you have 500 people each making one choice amongst 26 options; and 50 of them choose option 2; then 50/100 = 10% will have chosen option 2. If that's not the kind of result you are looking for, then I'd need to see a better example of what the data looks like, and what the results should look like, than I've seen so far. --ron That should be 50/500 = 10% --ron |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed...
I have a related question maybe someone can assist with and will be of some
use he My data is formatted so that column 1 as the 'bin value' and column 2 as the 'frequency ' that bin value occurs in the set. e.g. if the individual data were 5 5 5 6 6 6 6 7 7 8 8 8 8 8 etc.. my data format would be 5 3 6 4 7 2 8 5 basically the frequencies of my data have already been determined. I now want to calculate things (mean, sd, skew,kurtosis) on my data set,but only have this 2 column format to use. Excell needs all the data listed out. Isthere some way to quickly determine these descriptive stats? I ask because my data sets consist of 200-300 bin values, with frequencis between 10 and 300; and total frequencies (total number of data points in a data set ) is about 12,000 douglas "Steven Sinclair" wrote: I have a row of numbers from 1 to 26 in cells A1 to Z1 representing numbers 'chosen' at random by 500 unique people. I have another row of numbers representing the 'frequency' that the aforementioned number was chosen in cells A2 to Z2. Example: 1 2 3 4 5 6 7 8 9 10 11 ... 10 4 16 9 23 20 61 13 18 43 17 ... Is there a formula that I could use to calculate the percentage (0%-100%) that each number was chosen? Thanx. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed...
Hello Douglas,
I suggest to enter as UDF: Function one_dim_array2(rI As Range) As Variant 'Converts an argument array of frequencies and values into 'a one-dimensional array. 'Example: '3 25 '1 10 '4 12 '6 7 'will become 25 25 25 10 12 12 12 12 7 7 7 7 7 7. Dim vR As Variant Dim r As Range Dim b As Boolean Dim lF As Long, i As Long, j As Long j = 0 For i = 1 To rI.Rows.Count j = j + rI.Cells(i, 1).Value Next i ReDim vR(1 To j) i = 1 b = True For Each r In rI If b Then lF = r.Value Else Do While lF 0 vR(i) = r.Value i = i + 1 lF = lF - 1 Loop End If b = Not b Next r one_dim_array2 = vR End Function Then enter into any cell =AVERAGE(one_dim_array2($A$1:$B$4)) or other desired functions like MEDIAN, MODE, SKEW, KURT... Please notice that this UDF assumes frequencies first, then values (your example is the other way round). HTH, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Help Needed | Excel Discussion (Misc queries) | |||
Formula needed | Excel Discussion (Misc queries) | |||
More Help Needed with Count formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula needed | Excel Worksheet Functions |