Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting blank cells in a formula
I am trying to track progress with a percentage. In a range (V2:V90), I want
excel to count blank cells as 0 and if someone enters a date in the worksheet, I want excel to count as 1. Then I would take and divide the total in that cell range and by and overall total to arrive at a percentage. I've tried many things and can't get it to work. Here's an example of something I tried... =(IF(ISBLANK(V2:V90),"0")(SUM(V2:V90)/F173)) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting blank cells in a formula
One guess, going by your attempt:
=(IF(ISBLANK(V2:V90),"0")(SUM(V2:V90)/F173)) Maybe this: =IF(COUNTBLANK(V2:V90)=89,0,SUM(V2:V90)/F173) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Krashn" wrote: I am trying to track progress with a percentage. In a range (V2:V90), I want excel to count blank cells as 0 and if someone enters a date in the worksheet, I want excel to count as 1. Then I would take and divide the total in that cell range and by and overall total to arrive at a percentage. I've tried many things and can't get it to work. Here's an example of something I tried... =(IF(ISBLANK(V2:V90),"0")(SUM(V2:V90)/F173)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting blank cells in a formula
I am not sure where the F137 come into the story, but:
=SUM(V2:V90)/(COUNT(V2:V90)+COUNTBLANK(V2:V90)) The SUM will add up the ones and blanks The denominator divides by the total number of cells in the region (non-blanks plus blanks) -- Gary''s Student - gsnu200745 "Krashn" wrote: I am trying to track progress with a percentage. In a range (V2:V90), I want excel to count blank cells as 0 and if someone enters a date in the worksheet, I want excel to count as 1. Then I would take and divide the total in that cell range and by and overall total to arrive at a percentage. I've tried many things and can't get it to work. Here's an example of something I tried... =(IF(ISBLANK(V2:V90),"0")(SUM(V2:V90)/F173)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting blank cells in a formula
Well, this gives me a couple of ideas to try, and maybe I am overthinking
this, but what I need the formula to provide is not actually the totals of the cells, but the number of ones with dates versus ones with out. So when someone enters a date, that is one. The cell F173 is the total number of projects (also equal to the total number of cells) at 89. If I add an obejective to the list F173 will update with the new number. I want the formula to divide the number of date into the total for a percentage. thanks for the replies. I can probably figure a round about way to do it use formulas in several cells, but I want to be able to enter one formula into one cell to do that! "Gary''s Student" wrote: I am not sure where the F137 come into the story, but: =SUM(V2:V90)/(COUNT(V2:V90)+COUNTBLANK(V2:V90)) The SUM will add up the ones and blanks The denominator divides by the total number of cells in the region (non-blanks plus blanks) -- Gary''s Student - gsnu200745 "Krashn" wrote: I am trying to track progress with a percentage. In a range (V2:V90), I want excel to count blank cells as 0 and if someone enters a date in the worksheet, I want excel to count as 1. Then I would take and divide the total in that cell range and by and overall total to arrive at a percentage. I've tried many things and can't get it to work. Here's an example of something I tried... =(IF(ISBLANK(V2:V90),"0")(SUM(V2:V90)/F173)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting blank cells in a formula
Given this new information, the percentage of cells with data in your range
would be: =count(v2:v90)/f173 -- Regards, Fred "Krashn" wrote in message ... Well, this gives me a couple of ideas to try, and maybe I am overthinking this, but what I need the formula to provide is not actually the totals of the cells, but the number of ones with dates versus ones with out. So when someone enters a date, that is one. The cell F173 is the total number of projects (also equal to the total number of cells) at 89. If I add an obejective to the list F173 will update with the new number. I want the formula to divide the number of date into the total for a percentage. thanks for the replies. I can probably figure a round about way to do it use formulas in several cells, but I want to be able to enter one formula into one cell to do that! "Gary''s Student" wrote: I am not sure where the F137 come into the story, but: =SUM(V2:V90)/(COUNT(V2:V90)+COUNTBLANK(V2:V90)) The SUM will add up the ones and blanks The denominator divides by the total number of cells in the region (non-blanks plus blanks) -- Gary''s Student - gsnu200745 "Krashn" wrote: I am trying to track progress with a percentage. In a range (V2:V90), I want excel to count blank cells as 0 and if someone enters a date in the worksheet, I want excel to count as 1. Then I would take and divide the total in that cell range and by and overall total to arrive at a percentage. I've tried many things and can't get it to work. Here's an example of something I tried... =(IF(ISBLANK(V2:V90),"0")(SUM(V2:V90)/F173)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting non-blank cells | Excel Discussion (Misc queries) | |||
Counting blank cells | Excel Discussion (Misc queries) | |||
=SUMPRODUCT formula is counting the blank cells as well as zero's | Excel Worksheet Functions | |||
=SUMPRODUCT formula is counting the blank cells as well as zero's | Excel Worksheet Functions | |||
counting blank cells | Excel Discussion (Misc queries) |