Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional STDEV formula
I have an Access app that uses a crystal report and exports to excel. I was
using this function to get the standard and average deviations for a specific range once the info was in Excel: Sub CalcDeviations(objXLApp) Dim m As Long Dim x As Double Dim y As Double Dim myRange As Range Set myRange = Range("L5", Range("L5").End(xlDown)) x = objXLApp.WorksheetFunction.AveDev(myRange) y = objXLApp.WorksheetFunction.StDev(myRange) For m = Range("A65536").End(xlUp).Row To 1 Step -1 If Range("A" & m).Value = "Average Deviation" Then Range("L" & m).FormulaR1C1 = x & "%" Range("L" & m).NumberFormat = "##0.00%_)" EseIf Range("A" & m).Value = "Standard Deviation" Then Range("L" & m).FormulaR1C1 = y & "%" Range("L" & m).NumberFormat = "##0.00%_)" End If Range("L" & m).Select With Selection.Font .Bold = True .Name = "Times New Roman" .Size = 8 End With Next End Sub That worked perfect for me. The problem is that the user changed the information requested. There are now subtotals in the columns along with the data to calculate the deviations on. my question is how do I exclude those subtotals? My condition would be: If column A contains "RD" or "SFC", do not count those rows when calculating the deviations. This is my first post, so I apologize if I left anything out. Thanks in advance for any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional STDEV formula
Sub CalcDeviations()
Dim m As Long Dim x As Double Dim y As Double Dim myRange As Range Dim v As Variant, s as String Dim s1 as String, s2 as String Set myRange = Range("L5", Range("L5").End(xlDown)) s = myRange.Address(1, 1, xlA1) s1 = Replace(s, "L", "A") s2 = "If(((" & s1 & "=""RD"")+(" & s1 & "=""SPC""))=0," & s & ")" v = Evaluate(s2) x = Application.AveDev(v) y = Application.StDev(v) For m = Range("A65536").End(xlUp).Row To 1 Step -1 If Range("A" & m).Value = "Average Deviation" Then Range("L" & m).FormulaR1C1 = x & "%" Range("L" & m).NumberFormat = "##0.00%_)" EseIf Range("A" & m).Value = "Standard Deviation" Then Range("L" & m).FormulaR1C1 = y & "%" Range("L" & m).NumberFormat = "##0.00%_)" End If Range("L" & m).Select With Selection.Font .Bold = True .Name = "Times New Roman" .Size = 8 End With Next End Sub worked for me. -- Regards, Tom Ogilvy "Phil Trumpy" wrote: I have an Access app that uses a crystal report and exports to excel. I was using this function to get the standard and average deviations for a specific range once the info was in Excel: Sub CalcDeviations(objXLApp) Dim m As Long Dim x As Double Dim y As Double Dim myRange As Range Set myRange = Range("L5", Range("L5").End(xlDown)) x = objXLApp.WorksheetFunction.AveDev(myRange) y = objXLApp.WorksheetFunction.StDev(myRange) For m = Range("A65536").End(xlUp).Row To 1 Step -1 If Range("A" & m).Value = "Average Deviation" Then Range("L" & m).FormulaR1C1 = x & "%" Range("L" & m).NumberFormat = "##0.00%_)" EseIf Range("A" & m).Value = "Standard Deviation" Then Range("L" & m).FormulaR1C1 = y & "%" Range("L" & m).NumberFormat = "##0.00%_)" End If Range("L" & m).Select With Selection.Font .Bold = True .Name = "Times New Roman" .Size = 8 End With Next End Sub That worked perfect for me. The problem is that the user changed the information requested. There are now subtotals in the columns along with the data to calculate the deviations on. my question is how do I exclude those subtotals? My condition would be: If column A contains "RD" or "SFC", do not count those rows when calculating the deviations. This is my first post, so I apologize if I left anything out. Thanks in advance for any help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional STDEV formula
Thanks for the reply Tom. I can see basically what your code is attempting
to do, however, I get the same result as before. I am not sure I get this line: s2 = "If(((" & s1 & "=""RD"")+(" & s1 & "=""SPC""))=0," & s & ")" I noticed that you had written "SPC" instead of "SFC" in your code. Not a big deal, but when I copied your code in, I forgot to change it and got the same result as earlier. After I changed it to "SFC", again, same result. So, I am assuming that this line needs to be changed, but I don't quite understand what to change it too. Thanks again. Phil "Tom Ogilvy" wrote: Sub CalcDeviations() Dim m As Long Dim x As Double Dim y As Double Dim myRange As Range Dim v As Variant, s as String Dim s1 as String, s2 as String Set myRange = Range("L5", Range("L5").End(xlDown)) s = myRange.Address(1, 1, xlA1) s1 = Replace(s, "L", "A") s2 = "If(((" & s1 & "=""RD"")+(" & s1 & "=""SPC""))=0," & s & ")" v = Evaluate(s2) x = Application.AveDev(v) y = Application.StDev(v) For m = Range("A65536").End(xlUp).Row To 1 Step -1 If Range("A" & m).Value = "Average Deviation" Then Range("L" & m).FormulaR1C1 = x & "%" Range("L" & m).NumberFormat = "##0.00%_)" EseIf Range("A" & m).Value = "Standard Deviation" Then Range("L" & m).FormulaR1C1 = y & "%" Range("L" & m).NumberFormat = "##0.00%_)" End If Range("L" & m).Select With Selection.Font .Bold = True .Name = "Times New Roman" .Size = 8 End With Next End Sub worked for me. -- Regards, Tom Ogilvy "Phil Trumpy" wrote: I have an Access app that uses a crystal report and exports to excel. I was using this function to get the standard and average deviations for a specific range once the info was in Excel: Sub CalcDeviations(objXLApp) Dim m As Long Dim x As Double Dim y As Double Dim myRange As Range Set myRange = Range("L5", Range("L5").End(xlDown)) x = objXLApp.WorksheetFunction.AveDev(myRange) y = objXLApp.WorksheetFunction.StDev(myRange) For m = Range("A65536").End(xlUp).Row To 1 Step -1 If Range("A" & m).Value = "Average Deviation" Then Range("L" & m).FormulaR1C1 = x & "%" Range("L" & m).NumberFormat = "##0.00%_)" EseIf Range("A" & m).Value = "Standard Deviation" Then Range("L" & m).FormulaR1C1 = y & "%" Range("L" & m).NumberFormat = "##0.00%_)" End If Range("L" & m).Select With Selection.Font .Bold = True .Name = "Times New Roman" .Size = 8 End With Next End Sub That worked perfect for me. The problem is that the user changed the information requested. There are now subtotals in the columns along with the data to calculate the deviations on. my question is how do I exclude those subtotals? My condition would be: If column A contains "RD" or "SFC", do not count those rows when calculating the deviations. This is my first post, so I apologize if I left anything out. Thanks in advance for any help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional STDEV formula
Before I posted, It was tested and worked for me. I put RD and SPC in the
rows where there were subtotals in the data. They were excluded from the calculations and gave the same results as worksheet functions which were applied to the appropriate subsets of the data. Formula worked both in code and using the formula it produces in a worksheet. So no, I wouldn't think it needs to be changed except for changing the RD/SPC to match the strings in column A. Possibly those cells contain "RD " or " RD" for example which of course do not equal "RD" and those rows would not be excluded. -- Regards, Tom Ogilvy "Phil Trumpy" wrote in message ... Thanks for the reply Tom. I can see basically what your code is attempting to do, however, I get the same result as before. I am not sure I get this line: s2 = "If(((" & s1 & "=""RD"")+(" & s1 & "=""SPC""))=0," & s & ")" I noticed that you had written "SPC" instead of "SFC" in your code. Not a big deal, but when I copied your code in, I forgot to change it and got the same result as earlier. After I changed it to "SFC", again, same result. So, I am assuming that this line needs to be changed, but I don't quite understand what to change it too. Thanks again. Phil "Tom Ogilvy" wrote: Sub CalcDeviations() Dim m As Long Dim x As Double Dim y As Double Dim myRange As Range Dim v As Variant, s as String Dim s1 as String, s2 as String Set myRange = Range("L5", Range("L5").End(xlDown)) s = myRange.Address(1, 1, xlA1) s1 = Replace(s, "L", "A") s2 = "If(((" & s1 & "=""RD"")+(" & s1 & "=""SPC""))=0," & s & ")" v = Evaluate(s2) x = Application.AveDev(v) y = Application.StDev(v) For m = Range("A65536").End(xlUp).Row To 1 Step -1 If Range("A" & m).Value = "Average Deviation" Then Range("L" & m).FormulaR1C1 = x & "%" Range("L" & m).NumberFormat = "##0.00%_)" EseIf Range("A" & m).Value = "Standard Deviation" Then Range("L" & m).FormulaR1C1 = y & "%" Range("L" & m).NumberFormat = "##0.00%_)" End If Range("L" & m).Select With Selection.Font .Bold = True .Name = "Times New Roman" .Size = 8 End With Next End Sub worked for me. -- Regards, Tom Ogilvy "Phil Trumpy" wrote: I have an Access app that uses a crystal report and exports to excel. I was using this function to get the standard and average deviations for a specific range once the info was in Excel: Sub CalcDeviations(objXLApp) Dim m As Long Dim x As Double Dim y As Double Dim myRange As Range Set myRange = Range("L5", Range("L5").End(xlDown)) x = objXLApp.WorksheetFunction.AveDev(myRange) y = objXLApp.WorksheetFunction.StDev(myRange) For m = Range("A65536").End(xlUp).Row To 1 Step -1 If Range("A" & m).Value = "Average Deviation" Then Range("L" & m).FormulaR1C1 = x & "%" Range("L" & m).NumberFormat = "##0.00%_)" EseIf Range("A" & m).Value = "Standard Deviation" Then Range("L" & m).FormulaR1C1 = y & "%" Range("L" & m).NumberFormat = "##0.00%_)" End If Range("L" & m).Select With Selection.Font .Bold = True .Name = "Times New Roman" .Size = 8 End With Next End Sub That worked perfect for me. The problem is that the user changed the information requested. There are now subtotals in the columns along with the data to calculate the deviations on. my question is how do I exclude those subtotals? My condition would be: If column A contains "RD" or "SFC", do not count those rows when calculating the deviations. This is my first post, so I apologize if I left anything out. Thanks in advance for any help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional STDEV formula
You were right, Tom. I think it's one of those things where I was staring at
it for so long that I wasn't thinking straight. I hadn't trimmed column A. Once I did that, it worked perfectly. Thanks again for your help. Phil "Tom Ogilvy" wrote: Before I posted, It was tested and worked for me. I put RD and SPC in the rows where there were subtotals in the data. They were excluded from the calculations and gave the same results as worksheet functions which were applied to the appropriate subsets of the data. Formula worked both in code and using the formula it produces in a worksheet. So no, I wouldn't think it needs to be changed except for changing the RD/SPC to match the strings in column A. Possibly those cells contain "RD " or " RD" for example which of course do not equal "RD" and those rows would not be excluded. -- Regards, Tom Ogilvy "Phil Trumpy" wrote in message ... Thanks for the reply Tom. I can see basically what your code is attempting to do, however, I get the same result as before. I am not sure I get this line: s2 = "If(((" & s1 & "=""RD"")+(" & s1 & "=""SPC""))=0," & s & ")" I noticed that you had written "SPC" instead of "SFC" in your code. Not a big deal, but when I copied your code in, I forgot to change it and got the same result as earlier. After I changed it to "SFC", again, same result. So, I am assuming that this line needs to be changed, but I don't quite understand what to change it too. Thanks again. Phil "Tom Ogilvy" wrote: Sub CalcDeviations() Dim m As Long Dim x As Double Dim y As Double Dim myRange As Range Dim v As Variant, s as String Dim s1 as String, s2 as String Set myRange = Range("L5", Range("L5").End(xlDown)) s = myRange.Address(1, 1, xlA1) s1 = Replace(s, "L", "A") s2 = "If(((" & s1 & "=""RD"")+(" & s1 & "=""SPC""))=0," & s & ")" v = Evaluate(s2) x = Application.AveDev(v) y = Application.StDev(v) For m = Range("A65536").End(xlUp).Row To 1 Step -1 If Range("A" & m).Value = "Average Deviation" Then Range("L" & m).FormulaR1C1 = x & "%" Range("L" & m).NumberFormat = "##0.00%_)" EseIf Range("A" & m).Value = "Standard Deviation" Then Range("L" & m).FormulaR1C1 = y & "%" Range("L" & m).NumberFormat = "##0.00%_)" End If Range("L" & m).Select With Selection.Font .Bold = True .Name = "Times New Roman" .Size = 8 End With Next End Sub worked for me. -- Regards, Tom Ogilvy "Phil Trumpy" wrote: I have an Access app that uses a crystal report and exports to excel. I was using this function to get the standard and average deviations for a specific range once the info was in Excel: Sub CalcDeviations(objXLApp) Dim m As Long Dim x As Double Dim y As Double Dim myRange As Range Set myRange = Range("L5", Range("L5").End(xlDown)) x = objXLApp.WorksheetFunction.AveDev(myRange) y = objXLApp.WorksheetFunction.StDev(myRange) For m = Range("A65536").End(xlUp).Row To 1 Step -1 If Range("A" & m).Value = "Average Deviation" Then Range("L" & m).FormulaR1C1 = x & "%" Range("L" & m).NumberFormat = "##0.00%_)" EseIf Range("A" & m).Value = "Standard Deviation" Then Range("L" & m).FormulaR1C1 = y & "%" Range("L" & m).NumberFormat = "##0.00%_)" End If Range("L" & m).Select With Selection.Font .Bold = True .Name = "Times New Roman" .Size = 8 End With Next End Sub That worked perfect for me. The problem is that the user changed the information requested. There are now subtotals in the columns along with the data to calculate the deviations on. my question is how do I exclude those subtotals? My condition would be: If column A contains "RD" or "SFC", do not count those rows when calculating the deviations. This is my first post, so I apologize if I left anything out. Thanks in advance for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using a conditional in STDEV function | Excel Worksheet Functions | |||
Display formula Excel uses for internal functions (STDEV...) | Excel Worksheet Functions | |||
STDEV in a Formula for Calculated Field in Pivot Table | Excel Worksheet Functions | |||
STDEV | Excel Discussion (Misc queries) | |||
STDEV...HELP | New Users to Excel |