Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range.Average Problem
I'm trying to average I3:I & c + 2
but I keep getting zeros. Any ideas why? CSVCICAvg = LCV.WorksheetFunction.Average(LCV.Range("I3:I" & (c + 2))) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range.Average Problem
Change
LCV.WorksheetFunction.Average to WorksheetFunction.Average HTH, Barb Reinhardt "NewToVB" wrote: I'm trying to average I3:I & c + 2 but I keep getting zeros. Any ideas why? CSVCICAvg = LCV.WorksheetFunction.Average(LCV.Range("I3:I" & (c + 2))) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range.Average Problem
I don't know what LCV is a reference to, but I suspect
LCV.Range doesn't point where you think it does or LCV.Range is blank or LCV.Range doesn't contain any numbers (or contains numbers that are stored as text strings). if you try using Average in a cell going against the range you want averaged and get a valid answer, then you can disregard the stored as strings. -- Regards, Tom Ogilvy "NewToVB" wrote: I'm trying to average I3:I & c + 2 but I keep getting zeros. Any ideas why? CSVCICAvg = LCV.WorksheetFunction.Average(LCV.Range("I3:I" & (c + 2))) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range.Average Problem
Thanks for the comments. LCV is my excel worksheet, I'm using Visual Studio
(Visual Basic) rather than VBA. I have to have LCV in front to tell it which sheet. I have LCV.Range in several other places in my code and it works fine, but I think I must be coding the Average Function wrong. You can average a range right? I don't know why I'd be getting all zeros. "Tom Ogilvy" wrote: I don't know what LCV is a reference to, but I suspect LCV.Range doesn't point where you think it does or LCV.Range is blank or LCV.Range doesn't contain any numbers (or contains numbers that are stored as text strings). if you try using Average in a cell going against the range you want averaged and get a valid answer, then you can disregard the stored as strings. -- Regards, Tom Ogilvy "NewToVB" wrote: I'm trying to average I3:I & c + 2 but I keep getting zeros. Any ideas why? CSVCICAvg = LCV.WorksheetFunction.Average(LCV.Range("I3:I" & (c + 2))) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range.Average Problem
LCV.Range has numbers and is formatted as Numbers and not text... I averaged
it separtely from the code and I got a valid answer... what am I doing wrong :( "Tom Ogilvy" wrote: I don't know what LCV is a reference to, but I suspect LCV.Range doesn't point where you think it does or LCV.Range is blank or LCV.Range doesn't contain any numbers (or contains numbers that are stored as text strings). if you try using Average in a cell going against the range you want averaged and get a valid answer, then you can disregard the stored as strings. -- Regards, Tom Ogilvy "NewToVB" wrote: I'm trying to average I3:I & c + 2 but I keep getting zeros. Any ideas why? CSVCICAvg = LCV.WorksheetFunction.Average(LCV.Range("I3:I" & (c + 2))) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range.Average Problem
If I have it formatted like this:
LCV.Range("C2:C11").NumberFormat = "0.000" can it return negative numbers? because some of the numbers its averaging are negative?? Thanks in advance! "NewToVB" wrote: Thanks for the comments. LCV is my excel worksheet, I'm using Visual Studio (Visual Basic) rather than VBA. I have to have LCV in front to tell it which sheet. I have LCV.Range in several other places in my code and it works fine, but I think I must be coding the Average Function wrong. You can average a range right? I don't know why I'd be getting all zeros. "Tom Ogilvy" wrote: I don't know what LCV is a reference to, but I suspect LCV.Range doesn't point where you think it does or LCV.Range is blank or LCV.Range doesn't contain any numbers (or contains numbers that are stored as text strings). if you try using Average in a cell going against the range you want averaged and get a valid answer, then you can disregard the stored as strings. -- Regards, Tom Ogilvy "NewToVB" wrote: I'm trying to average I3:I & c + 2 but I keep getting zeros. Any ideas why? CSVCICAvg = LCV.WorksheetFunction.Average(LCV.Range("I3:I" & (c + 2))) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range.Average Problem
WorksheetFunction is a member of the Application object. Try:
CSVCICAvg = xlApp.WorksheetFunction.Average(LCV.Range("I3:I" & (c + 2))) where xlApp is a variable that holds the application object. If you don't have a variable for the application, try: CSVCICAvg = LCV.Application.WorksheetFunction.Average(LCV.Rang e("I3:I" & (c + 2))) -- Hope that helps. Vergel Adriano "NewToVB" wrote: Thanks for the comments. LCV is my excel worksheet, I'm using Visual Studio (Visual Basic) rather than VBA. I have to have LCV in front to tell it which sheet. I have LCV.Range in several other places in my code and it works fine, but I think I must be coding the Average Function wrong. You can average a range right? I don't know why I'd be getting all zeros. "Tom Ogilvy" wrote: I don't know what LCV is a reference to, but I suspect LCV.Range doesn't point where you think it does or LCV.Range is blank or LCV.Range doesn't contain any numbers (or contains numbers that are stored as text strings). if you try using Average in a cell going against the range you want averaged and get a valid answer, then you can disregard the stored as strings. -- Regards, Tom Ogilvy "NewToVB" wrote: I'm trying to average I3:I & c + 2 but I keep getting zeros. Any ideas why? CSVCICAvg = LCV.WorksheetFunction.Average(LCV.Range("I3:I" & (c + 2))) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range.Average Problem
Ok, I figured out whats going on.... but I'm not sure how to correct it. The
reason I'm getting all zeros is because either my numbers are less than 0.5 or they are negative. I don't know why its rounding. Any ideas on how to correct this? "Tom Ogilvy" wrote: I don't know what LCV is a reference to, but I suspect LCV.Range doesn't point where you think it does or LCV.Range is blank or LCV.Range doesn't contain any numbers (or contains numbers that are stored as text strings). if you try using Average in a cell going against the range you want averaged and get a valid answer, then you can disregard the stored as strings. -- Regards, Tom Ogilvy "NewToVB" wrote: I'm trying to average I3:I & c + 2 but I keep getting zeros. Any ideas why? CSVCICAvg = LCV.WorksheetFunction.Average(LCV.Range("I3:I" & (c + 2))) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range.Average Problem
Thanks everyone for the help... I thought you might like to know that I
solved it... I guess its been a long day of coding...but I had CSVCICAvg Dimmed as Integer rather than Double and it was rounding to zeros... Sorry about that, but thanks for the help! "Vergel Adriano" wrote: WorksheetFunction is a member of the Application object. Try: CSVCICAvg = xlApp.WorksheetFunction.Average(LCV.Range("I3:I" & (c + 2))) where xlApp is a variable that holds the application object. If you don't have a variable for the application, try: CSVCICAvg = LCV.Application.WorksheetFunction.Average(LCV.Rang e("I3:I" & (c + 2))) -- Hope that helps. Vergel Adriano "NewToVB" wrote: Thanks for the comments. LCV is my excel worksheet, I'm using Visual Studio (Visual Basic) rather than VBA. I have to have LCV in front to tell it which sheet. I have LCV.Range in several other places in my code and it works fine, but I think I must be coding the Average Function wrong. You can average a range right? I don't know why I'd be getting all zeros. "Tom Ogilvy" wrote: I don't know what LCV is a reference to, but I suspect LCV.Range doesn't point where you think it does or LCV.Range is blank or LCV.Range doesn't contain any numbers (or contains numbers that are stored as text strings). if you try using Average in a cell going against the range you want averaged and get a valid answer, then you can disregard the stored as strings. -- Regards, Tom Ogilvy "NewToVB" wrote: I'm trying to average I3:I & c + 2 but I keep getting zeros. Any ideas why? CSVCICAvg = LCV.WorksheetFunction.Average(LCV.Range("I3:I" & (c + 2))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AVERAGE a range in a column if another column's range equals a val | Excel Discussion (Misc queries) | |||
Zero in Average problem | Excel Discussion (Misc queries) | |||
Problem with average | Excel Discussion (Misc queries) | |||
Average Problem | Excel Worksheet Functions | |||
AVERAGE problem | Excel Worksheet Functions |