Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate the average by searching to cell values
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- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate the average by searching to cell values
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- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate the average by searching to cell values
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- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate the average by searching to cell values
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- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate the average by searching to cell values
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" 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- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate the average by searching to cell values
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 -- Regards, Tom Ogilvy "Metin" wrote in message ... 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" 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- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate the average by searching to cell values
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. "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Metin" wrote in message ... 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" 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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate rolling average using last 30 values greater than 0. | Excel Discussion (Misc queries) | |||
Calculate average with missing values | Excel Worksheet Functions | |||
ignore MAX and MIN values in a set to calculate average | Excel Discussion (Misc queries) | |||
Calculate average and not include zero values | Excel Discussion (Misc queries) | |||
Looking-up Columns w/calc'd Values ONLY to Calculate Average | Excel Worksheet Functions |