🔒 Locked

Excel Question

Started by MTT Apr 4, 2009 at 7:56 PM 1 replies 1.2k views

Original Post

Apr 4, 2009 at 7:56 PM

Wasn't sure where to post this, but hopefully somebody here can help. In excel I have a series of 100 random numbers from 1 to 6 and the corresponding values for Y = 1000*X+100*X^0.5. In the table below I did a count of each occurrence to see if everything was working properly using Count=COUNTIF(Y1:Y100,Y)and I was getting a sum between 96 and 99 as opposed to 100. I figured that this was probably due to some sort of rounding error.


X     P(x)     Y            Count
1     0.05     1100.000     5
2     0.20     2141.421     18
3     0.20     3173.205     15
4     0.25     4200.000     28
5     0.20     5223.607     22
6     0.10     6244.949     9
	       Sum:         97

I tried to replace the function with Count=COUNTIF(ROUND(Y1:Y100,0),ROUND(Y,0)) to eliminate that problem but it will not let me because it says there is an error. I'm not sure what the problem is with that function, can you guys see anything wrong with it or is this excels fault? To continue to check if it was in fact some problem with rounding when using very precise numbers I used ROUND(Y,-1) for all my values and then compared them and I am still getting sums between 96 and 99. I am really confused as to why this is happening, maybe I am missing something obvious, I don't know. Can any of you guys help me out with this?

Zipster

Apr 4, 2009 at 9:15 PM

You need one column 'A' that enumerates the possible values, and a second column 'B' that are the values actually chosen:

A    B1    42    33    34    15    56    2     1     6     .     .     .

Column 'C' would then count the number of occurrences by doing =COUNTIF($B$1:$B$100,A1) for 'C1', =COUNTIF($B$1:$B$100,A2) for 'C2', etc., which you can add together. The '$' sign fixes the counting range so that you can write the formula once and then just drag it down and have the cell value you're searching for update automatically.

MTT

Apr 4, 2009 at 10:07 PM

Haha, yeah I was definitely missing something obvious.

I already had my list of 100 random variables for column B but I didn't think it would make my post look very nice if I included them. But yeah, I forgot to lock in the range so for X = 6 was only counting from the last 95 RVs. Thanks for the help, although I'm a little disappointed in myself for not spotting that.

Not that I need it now, but I still am not sure what the problem is with the formula "=COUNTIF(ROUND(Y1:Y100,0),ROUND(Y,0))". After testing out a few different combination I found that COUNTIF(range,criteria) would accept a function for its second argument but not for its first.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.