Help Needed

[cionnaith]

Hi.

I am trying to make some cells perform some calculations and am running into a snag.

I have three cells..each represent a term (e.g. Fall, Winter, Spring). Within each of the cells, I created three drop-down choices (i.e. "Proficient", "In Process" and "Not Yet"). "Proficient" has a value of 3, "In Process" = 2, and "Not Yet" = 1. In nearby cells, I need to calcuate the percentage of total for each of the three terms, based on the entry. For instance, if someone selects "Not Yet" in cell A1, the value in the other cell (e.g. A4) should be 1 divided by the total possible, which is 3 in that cell. Thus, A4 should show 33%. I need to do that for each of the three term cells (i.e. A1, A2, and A3) in A4, A5 and A6, respectively. Finally, I need a cell (A7) that calculates the total percentage across the three percentage cells. Given that the highest ranking (i.e. "Proficient") carries a value of 3, the total possible points for all three cells would be 9.

The trick is...if there is nothing selected in one, or two of the three entry cells, I need the total cell to ignore any empty cells (of the three) and only calculate if there are entries.

This is what i did for the cells that calculate the percentage based on what is selected from the drop down list (i.e. cell A4)

=IF(A1="Proficient",3,IF(A1="In Process",2,IF(A1="Not Yet",1,"")))/3

I have that set up for cells A2 and A3. Unfortunately, if one of those cells has no selection, I get a #VALUE instead of a blank cell.

I hope all this makes sense. The bottom line is that any cells that have no entries should result in an blank computation cell.

Thanks in advance folks.

 

[Jonmo1]

Instead of a blank cell returned, would a 0 be sufficient?

change
=IF(A1="Proficient",3,IF(A1="In Process",2,IF(A1="Not Yet",1,"")))/3
to
=IF(A1="Proficient",3,IF(A1="In Process",2,IF(A1="Not Yet",1,0)))/3

 

[cionnaith]

Instead of a blank cell returned, would a 0 be sufficient?

change
=IF(A1="Proficient",3,IF(A1="In Process",2,IF(A1="Not Yet",1,"")))/3
to
=IF(A1="Proficient",3,IF(A1="In Process",2,IF(A1="Not Yet",1,0)))/3

Thanks for the reply. I don't want a zero for a blank cell because when the total calculation is done across the three cells, the zero carries numerical weight. Ultimately, I would like the "total" cell to calculate the total percentage across the three cells, but one of those cells may not have an entry. In that case, I just need the total percentage for what entries are present. If a zero is given for an empty cell, the zero would carry weight when the total percentage across the three cells is calculated.

 

[Jonmo1]

Then try

=IF(A1="","",IF(A1="Proficient",3,IF(A1="In Process",2,IF(A1="Not Yet",1,"")))/3)

 

[cionnaith]

Then try

=IF(A1="","",IF(A1="Proficient",3,IF(A1="In Process",2,IF(A1="Not Yet",1,"")))/3)

Thank you so much! That works perfect for the cells that calculate the percentages (i.e. A4, A5 and A6) from the entries made in the term cells with the drop-down choices (i.e. A1, A2 and A3).

Now all I need is one more cell (e.g. A7) that will calculate the total percentage across A4, A5, and A6, based on entries made in A1, A2, and A3, ignoring any empty cells. Can that total cell be created such that if there is only one entry in the three cells that the entry value is divided by 3, if two cells have entries the entry values will be divided by 6, and if there are three cells with entry values, it will be divided by 9? Hope this makes sense

Thanks so much again for your help and quick replies!

 

[Jonmo1]

Perhaps

=SUM(A4:A6)/(COUNT(A4:A6)*3)

 

[cionnaith]

Perhaps

=SUM(A4:A6)/(COUNT(A4:A6)*3)

That doesn't work. If there are no entries in any of the entry cells (i.e. A1, A2 and A3), that results in a #DIV/0!. Again, if no entries are made, that cell needs to be blank.

If there are entries, the percentage it gives isn't correct. For example, if I have "Proficient" entered in two of the three term cells (i.e. A1, A2) that results in 100% for both of the percentage cells (i.e. A4 and A5), which is what should be there. However, with the formula you just gave, A7 is showing 33.3%. It should show 100%.

I am probably not explaining it very well. That A7 cell should show the total percentage across the three cells. If all three entry cells have "proficient" entered, it will be 100%, but it should also show 100% if only one, or two of those entry cells have "proficient" entered. If, of the three entry cells, there are only two entries...one "not yet" and one "proficient". the A7 total percentage cell should show 67%.

Basically, I need the cell to show the total percentage of whatever is entered, ignorning blank cells and being blank if there are no entries in any of the three entry cells.

Thanks and sorry to be a pain

 

[Jonmo1]

Try

=IF(COUNT(A4:A6)=0,"",SUM(A4:A6)/(COUNT(A4:A6)*3))

 

[cionnaith]

Try

=IF(COUNT(A4:A6)=0,"",SUM(A4:A6)/(COUNT(A4:A6)*3))

That worked to return an empty cell, but it wasn't calculating the percentages correctly. I removed the "*3" and it is perfect.

Thank you so much for all of your help!