Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formula to count blank cells
Need help to create a formula to show percentage of how many time
each person attended training each year - requirement is once a month. Down column "A" are their names, columns "B1" thru "M1" are the 12 months. If they show for their monthly class I place an X in the cell if not I leave it blank. In Coulmn "N" for each person I am looking to enter a formula that will basically count the blank cells - subtract that from 12 and then divide that value by 12 which should give me the percentage of times that they showed up for class that year. If that makes sense ! Thanks - George |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formula to count blank cells
=(12-COUNTBLANK(B1:M1))/12
-- Gary''s Student - gsnu200904 "George" wrote: Need help to create a formula to show percentage of how many time each person attended training each year - requirement is once a month. Down column "A" are their names, columns "B1" thru "M1" are the 12 months. If they show for their monthly class I place an X in the cell if not I leave it blank. In Coulmn "N" for each person I am looking to enter a formula that will basically count the blank cells - subtract that from 12 and then divide that value by 12 which should give me the percentage of times that they showed up for class that year. If that makes sense ! Thanks - George |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formula to count blank cells
Try the below and format the formula cell to %
=COUNTIF(B1:M1,"X")/12 OR =COUNTA(B1:M1)/12 If this post helps click Yes --------------- Jacob Skaria "George" wrote: Need help to create a formula to show percentage of how many time each person attended training each year - requirement is once a month. Down column "A" are their names, columns "B1" thru "M1" are the 12 months. If they show for their monthly class I place an X in the cell if not I leave it blank. In Coulmn "N" for each person I am looking to enter a formula that will basically count the blank cells - subtract that from 12 and then divide that value by 12 which should give me the percentage of times that they showed up for class that year. If that makes sense ! Thanks - George |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formula to count blank cells
If I understand you correctly;
Cells(1, ActiveCell.Column) will give you the value of cell 1 or Row1 of the active cell/column reportWS.Range("D" & reportCurrentRow) = Cells(1, ActiveCell.Column) If this post helps click Yes --------------- Jacob Skaria "George" wrote: Need help to create a formula to show percentage of how many time each person attended training each year - requirement is once a month. Down column "A" are their names, columns "B1" thru "M1" are the 12 months. If they show for their monthly class I place an X in the cell if not I leave it blank. In Coulmn "N" for each person I am looking to enter a formula that will basically count the blank cells - subtract that from 12 and then divide that value by 12 which should give me the percentage of times that they showed up for class that year. If that makes sense ! Thanks - George |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formula to count blank cells
Sorry. please ignore the below post..
-- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: If I understand you correctly; Cells(1, ActiveCell.Column) will give you the value of cell 1 or Row1 of the active cell/column reportWS.Range("D" & reportCurrentRow) = Cells(1, ActiveCell.Column) If this post helps click Yes --------------- Jacob Skaria "George" wrote: Need help to create a formula to show percentage of how many time each person attended training each year - requirement is once a month. Down column "A" are their names, columns "B1" thru "M1" are the 12 months. If they show for their monthly class I place an X in the cell if not I leave it blank. In Coulmn "N" for each person I am looking to enter a formula that will basically count the blank cells - subtract that from 12 and then divide that value by 12 which should give me the percentage of times that they showed up for class that year. If that makes sense ! Thanks - George |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formula to count blank cells
They all work - Always more then one way to skin a cat !
Thanks - Greatly Appreciated "George" wrote: Need help to create a formula to show percentage of how many time each person attended training each year - requirement is once a month. Down column "A" are their names, columns "B1" thru "M1" are the 12 months. If they show for their monthly class I place an X in the cell if not I leave it blank. In Coulmn "N" for each person I am looking to enter a formula that will basically count the blank cells - subtract that from 12 and then divide that value by 12 which should give me the percentage of times that they showed up for class that year. If that makes sense ! Thanks - George |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTA ----- FORMULA WILL NOT COUNT NON BLANK CELLS | Excel Discussion (Misc queries) | |||
Befuddled by Count blank cells Formula! | Excel Worksheet Functions | |||
How to count blank cells that contain formula? | Excel Discussion (Misc queries) | |||
how can I count blank cells as a zero, when using formula please?? | Excel Worksheet Functions | |||
formula to count cells not blanK | Excel Worksheet Functions |