Calculate the average by searching to cell values

[Metin]

Hi all,

I want to search for specific cell values and calculate the average af the
data's and have the solution in another worksheet in a specific cell. And I
have to do this 4 times.

Example:

A B C D E
1 1 R2 A23C 17 26
2 1 R2 A23Q 25 24
3 1 R2 Blank 8 10
4 1 R2 A23M 19 18
5 1 R2 Blank 7 11

I have "Sheet1" with a lot of sample data. In column "C" the sample ID is
presented. In the first 97 rows, I want to find all the rows, which have the
text "Blank" in Column "C". In our example that would be rows 3 and 5. Now I
want to calculate the average of the data's in column D and the rows found
with "Blank". That would be D3 and D5. And the solution has to be presented
in "Sheet2" in cell "A8". The calculation I want to do 4 times. The average
of the data in column E has to be in cell "A9", the average of column F in
"A10" and the average of column G in "A11".

Can anybody please help me with this.

Thanks
-Metin-

 

[K Dales]

I often have similar data tables with the need to calculate averages off
criteria in multiple columns. The average is the sum divided by the count,
so it can be done with SUMIF and COUNTIF, e.g:
=SUMIF(Sheet1!C1:C97,"",Sheet1!D197)/COUNTIF(Sheet1!C1:C97,"")

 

[Metin]

How can I do this with VBA?

I often have similar data tables with the need to calculate averages off
criteria in multiple columns. The average is the sum divided by the count,
so it can be done with SUMIF and COUNTIF, e.g:
=SUMIF(Sheet1!C1:C97,"",Sheet1!D197)/COUNTIF(Sheet1!C1:C97,"")

 

[Tom Ogilvy]

set rng = Range("Sheet1!D197")

avg = application.Sumif(rng.offset(0,-1),"blank",rng)/ _
application.countif(rng.offset(0,-1),"blank")
worksheets("Sheet2").Range("A8").Value = avg

to put in a formula

worksheets("Sheet2").Range("A8").Formula = _
="SUMIF(Sheet1!C1:C97,""blank"",Sheet1!D197)/" _
"COUNTIF(Sheet1!C1:C97,""blank"")"

 

[Metin]

This is not working with me.

set rng = Range("Sheet1!D197")

avg = application.Sumif(rng.offset(0,-1),"blank",rng)/ _
application.countif(rng.offset(0,-1),"blank")
worksheets("Sheet2").Range("A8").Value = avg

to put in a formula

worksheets("Sheet2").Range("A8").Formula = _
="SUMIF(Sheet1!C1:C97,""blank"",Sheet1!D197)/" _
"COUNTIF(Sheet1!C1:C97,""blank"")"

 

[Metin]

With a little change it does work. Thanks Tom.
I have made the next macro, but I need some help for the next step.

Sub Testi()
Set exSh = Worksheets("raw data from spad it")
Set wks = Worksheets("Calculated Data")

exSh.Select
Set rng = Range("C2:S97")
avg = Application.SumIf(rng, "Blank", rng.Offset(0, 12)) / _
Application.CountIf(rng, "Blank")
wks.Select
Range("AL4").Value = avg
End Sub

With this macro the average of the values in the 12th column after the
column with the text "Blank" is calculated and the solution is put in cell
AL4. But now I want to do the same calculation for the 13th column and put
the solution of this in cell AM4, etc. So everything shifts 1 column to the
right.
How can I do the same calculation 4 times and every time shift 1 column to
the right.

Thanks,
-Metin-

 

[Tom Ogilvy]

Sub Testi_1()
Set exSh = Worksheets("raw data from spad it")
Set wks = Worksheets("Calculated Data")

j = 12
For i = 1 to 4
exSh.Select
Set rng = Range("C2:S97")
avg = Application.SumIf(rng, "Blank", rng.Offset(0, j)) / _
Application.CountIf(rng, "Blank")
wks.Select
Range("AL4").offset(0,i-1).Value = avg
j = j + 1
Next
End Sub

 

[Metin]

Thanks Tom it's working perfect.

Can you please help me with the next step.
In the macro, we have selected the first 96 rows (C2:S97) and did the
calculation. Because each batch of data includes 96 datarows. Now I want to
do the same calculation for the next databatch (C98:S193) if there is any
second databatch.

If C98 = "data" then do previous calculation for the second range
(C98:S192), and present the solution 96 rows lower then "AL4".
ElseIf C193 = "data" then do previous calculation for the third range
(C193:S289), and present the solution 2*96 rows lower then "AL4".
And So On........
Else stop calculation.