# Help with excel calculation

#### lostboyca

##### Board Regular

% Comp. (H10) = I need to do a calculation such as: IF there is text in a cell J11:O26 count each cell as one THEN DIVIDE by the number of cells in J11:O26 which will give me the percentage.

This will be the start but there are more calculations but this will help me start it out.

Thanks

lostboyca

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Chitosunday

##### Well-known Member
=(COUNTA(J11:O26)+COUNTBLANK(J11:O26)) will give total
to get the percentage use the first formula then divide
like this
=COUNTA(J11:O26)/(COUNTA(J11:O26)+COUNTBLANK(J11:O26))

#### lostboyca

##### Board Regular
Thanks for the calculation now I have another question.

I need another calculation for H5 I would like it to calculate the following

I would like it to count all cells containing the text Pass in J11:O26 plus J29:O36 plus J39:O59 then divide all cells with and without text on J11:O26 plus J29:O36 plus J39:O59 which should give me the average

Thanks,

lostboyca

#### Yogi Anand

##### MrExcel MVP
Hi lostboyca:

How about the following array formula ...

=(COUNTIF(J11:O26,"pass")+COUNTIF(J29:O36,"pass")+COUNTIF(J39:O59,"pass"))/(COUNTA(J11:O59&"")-COLUMNS(J:O)*(ROWS(1:4)))

#### Chitosunday

##### Well-known Member

Here is a formula without using arrays

=COUNTA(J11:O26,J29:O36,J39:O59)/(COUNTA(J11:O26,J29:O36,J39:O59)+COUNTBLANK(J11:O26,J29:O36,J39:O59))

#### Domenic

##### MrExcel MVP
Try...

=AVERAGE(IF(1-ISNUMBER(MATCH(ROW(J11:O59)-ROW(J11)+1,{17,18,27,28},0)),(J11:O59="Pass")+0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

#### lostboyca

##### Board Regular

Yogi Anand/Domenic –

Your script works now the question I have is that can you break down the script in English? I want to add more rows and cells to the calculation.

Chitosunday –
Your script did not work it error out and highlighted COUNTBLANK(J11:O26,J29:O36,J39:O59))

Thanks,

Lostboyca

#### Domenic

##### MrExcel MVP
lostboyca said:
Your script works now the question I have is that can you break down the script in English? I want to add more rows and cells to the calculation.

With regards to my formula...

1) replace the references to J11:O59 with references to your new range, and

2) change the array constant {17,18,27,28} to specify which row, relative to the first row in your table, to exclude from the calculation. In this case, rows 17, 18, 27, and 28 are excluded from the calculation.

#### lostboyca

##### Board Regular
Great thanks for all the help!!!! Replies
10
Views
137
Replies
7
Views
42
Replies
1
Views
113
Replies
1
Views
108
Replies
3
Views
380