If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating Problem: Desperate :(
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. |
#2
|
|||
|
|||
Calculating Problem: Desperate :(
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 |
#3
|
|||
|
|||
Calculating Problem: Desperate :(
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? "Ed Ferrero" wrote: 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 . |
#4
|
|||
|
|||
Calculating Problem: Desperate :(
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 "Ed Ferrero" wrote: 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 . |
#5
|
|||
|
|||
Calculating Problem: Desperate :(
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 "Ed Ferrero" wrote: 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 . |
Thread Tools | |
Display Modes | |
|
|