Calculating Problem: Desperate :(

[cionnaith]

Hi. I have run into a snag trying to figure out how to make one of my cells
calculate the way I need it to. Here is the rundown: I have a column of data
that needs to have only 0,1,2,3, or 4 entered in each cell. These values
actually represent qualitative data. At the end of the column I need to count
how many variable values were entered across 20 cells (e.g. 10 out of 20, or
all 20). The syntax for that function I used is =COUNTIF(D1231,">=0"),
which works. Now the area that I am having trouble is that I need another
cell that will calculate the total percentage for the column based on those
0,1,2,3, or 4 entries in the column. I previously used 1, 2, 3 or 4 as the
variable entries and all was well with this syntax to calculate the
percentage =SUM(D1231)/(D32*4). Now that I have had to use "0" as a
variable value, I can't get the cell to count it among the rest of the
entries to calculate the percentage. Given that I now have 5 variable
entries, I tried changing the *4 to *5, but the "0's" are not being counted.
In short, what I need to happen is if all of the entries in the column of 20
cells are 0's the first cell function I described should result in the number
20. Then I need the total calculated percentage in the other cell to be 20%
for all 0's. It would be 40% if all 1's, 60% if all 2's, 80% if all 3's and
100% if all 4's. I hope this makes sense.

 

[Ed Ferrero]

Hi cionnaith,

In short, what I need to happen is if all of the entries in the column of
20
cells are 0's the first cell function I described should result in the
number
20. Then I need the total calculated percentage in the other cell to be
20%
for all 0's. It would be 40% if all 1's, 60% if all 2's, 80% if all 3's
and
100% if all 4's. I hope this makes sense.

First Cell function, No of variables entered
=COUNT(D1231)

Second cell function, Total calculated percentage
=SUM(IF(ISBLANK(D1231),0,(D1231+1)/5))/ROWS(D1231)

This is entered as an array function, i.e. enter this in a cell then press
Ctrl-Shift-Enter
You have done it right when the formula is surounded by curly brackets
{=SUM(IF(ISBLANK(D1231),0,(D1231+1)/5))/ROWS(D1231)}

Ed Ferrero
www.edferrero.com

 

[cionnaith]

Thank you so much...That did it!! Now, I have to do the same thing, but
going across columns. I tried doing it, but it didn't work. Tried changing
the /ROWS to /COLUMNS, but that didn't work either. What am I doing wrong
there?

 

[cionnaith]

Actually, I figured it out. I didn't enter it in the cell by doing ctl shift
enter

Thank you again. You have just made my wife a very happy woman because I can
finish this up

 

[cionnaith]

Hi Ed.

One more question. As I mentioned before, I figured out how to do it
across columns. The last thing I need to do is to fundamentally do the same
thing, but across both rows and columns as a total % for the entire field
(d12:g31). I have been playing around trying to figure it out and can't

Thanks in advance