Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]() I'm trying to create a formula that involves counting and percents. So, in the range A9:AE9, I want to count the number of times "y" appears (each cell of the range has a "y", "n", or "n/a") and divide that number by 31 (the number of columns in the range). It sounds simple, but I can't figure it out! Any help is appreciated. Thanks! -- blazon ------------------------------------------------------------------------ blazon's Profile: http://www.excelforum.com/member.php...o&userid=27409 View this thread: http://www.excelforum.com/showthread...hreadid=469224 |
#2
![]() |
|||
|
|||
![]() Try this: =COUNTIF(A9:AE9,"=y")/31 -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=469224 |
#3
![]() |
|||
|
|||
![]() Hey, that works, thanks! I just realized that I don't want the 'n/a' to negatively impact the percent. I'm basically tracking goals and so ideally, if everyone meets their goal, they will achieve 100%. If a 'n/a' is recorded, is there a way then that I can not have that cell counted in the calculation of the percent? So, if there are 3 'n/a's, then rather than dividing by 31, excel would divide by 28? Thanks so much for your help! -- blazon ------------------------------------------------------------------------ blazon's Profile: http://www.excelforum.com/member.php...o&userid=27409 View this thread: http://www.excelforum.com/showthread...hreadid=469224 |
#4
![]() |
|||
|
|||
![]() Remember, you are dividing by the number of columns. Is there only one entry per column, therefore, an N/A makes that column not part of the group? -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=469224 |
#5
![]() |
|||
|
|||
![]() Yes, that's correct. -- blazon ------------------------------------------------------------------------ blazon's Profile: http://www.excelforum.com/member.php...o&userid=27409 View this thread: http://www.excelforum.com/showthread...hreadid=469224 |
#6
![]() |
|||
|
|||
![]() Then subtract the number of "n/a" from the count of columns (31), as such: =COUNTIF(A9:AE9,"=y")/(31-COUNTIF(A9:AE9,"=n/a")) Should work. -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=469224 |
#7
![]() |
|||
|
|||
![]()
=COUNTIF(A9:AE9,"=y")/(COUNTA(A9:AE9)-COUNTIF(A9:AE9,"=n/a"))
or =COUNTIF(A9:AE9,"=y")/(31-COUNTIF(A9:AE9,"=n/a")) (If the number of columns is always 31) These formulas should work. "blazon" wrote: Hey, that works, thanks! I just realized that I don't want the 'n/a' to negatively impact the percent. I'm basically tracking goals and so ideally, if everyone meets their goal, they will achieve 100%. If a 'n/a' is recorded, is there a way then that I can not have that cell counted in the calculation of the percent? So, if there are 3 'n/a's, then rather than dividing by 31, excel would divide by 28? Thanks so much for your help! -- blazon ------------------------------------------------------------------------ blazon's Profile: http://www.excelforum.com/member.php...o&userid=27409 View this thread: http://www.excelforum.com/showthread...hreadid=469224 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Formula works in some cells, doesn't in other | New Users to Excel | |||
How do I write formula to compare two values and pull the resulta. | Excel Worksheet Functions | |||
how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
How do I write my formula to automatically regenerate a RAND () i. | Excel Worksheet Functions |