Calculate the average by searching to cell values
This is not working with me.
"Tom Ogilvy" wrote:
set rng = Range("Sheet1!D1:D97")
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!D1:D97)/" _
"COUNTIF(Sheet1!C1:C97,""blank"")"
--
Regards,
Tom Oglvy
"Metin" wrote in message
...
How can I do this with VBA?
"K Dales" wrote:
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!D1:D97)/COUNTIF(Sheet1!C1:C97,"")
"Metin" wrote:
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-
|