I have a problem with a Macro i am writing... I will explain what it does to give you an understanding of my problem. First of all it opens two text files and reads the values into column B and then column C. Then it draws a graph based on the data in column A, B & C, then I had to write some code that looks for the start and the end of a certain event within each data set (or column) when it has found the start and end points, it calculates the Average of all the values in between... my problem is that i NEVER get the same result as when Excel does it with the Average function, I thought it could be because my data contans negative values and because of adding them it was subtracting them, i fixed that but its still doing it..... i am at a loss, i am a pretty experienced VB type guy and i wont let this macro beat me!!!

Average functiun takes all values except blank and not numbers values maybe some datas are not numbers recognized... its an arithmetic average

...er, why wouldn't you want the negative values to be added (ie. absolute values subtracted) to calculate your average...? average of 4, 6, -1 = 3 in my textbook

Because the values i am averaging is Electrical Current, so if it is a negative value i change it to positive with the ABS(value) function. i need to add ALL the values in a positive fashion!!!

Ahhhh - electrical stuff. Not really my thing. However, I'll still give your problem a go: Why not use an array formula? These can often be the unsung hero of the spreadsheet. Working their magic, day in, day out, they provide lovely answers where mere formulae stagger and falter. Try =AVERAGE(ABS(myRange)) and instead of just hitting Enter, hit CTRL+SHIFT+Enter. The problem here is that blanks in the range are treated as zero - but, if you want to get around that, then "Hey Presto!" - an ARRAY FORMULA comes to your aid!!!!!!! (Can you tell I'm bored here at work, yet?) Try: =SUM(ABS(MyRange))/COUNTA(MyRange) and again, get busy with the CTRL+SHIFT+Enter. Hope that helps. The VB coding for entering array formulae is straightforward, too - I'll let you look it up yourself! Cheers Tom

Yes, i have another problem... the data i use is read from a text file and since its always different i have coded the Macro to search for the start and end of a certain pattern of data... ie a range and so i have to build a cell reference for column B like this ("B" & Start : "B" & End) where Start and End are the "number" of the cell row i dont think Excel allows me to enter a range built like that...also what data type is MyRange as i believe i need to use the "Set" keyword to assign a range to MyRange....when i try that i get an error.....i dont think i have explained myself very well so please ask if/when you get confused!!!

OK - we're getting a bit confused here. "MyRange" is just a variable placeholder - put in whatever you feel like, or even define a range as MyRange if you want. BUT REMEMBER: this is an example of the SPREADSHEET FUNCTION, and not the relevant VBA. If you want to refer to ranges using VBA, then I find the following the best way to do it: Rather than identify cell "A1" as Range("A1"), try Cells(1, 1). When you are happy with that, move onto a range, as defined like this: Range(Cells(1, 1), Cells(3, 5)) So, you can see that to identify a block of any dimension is as easy as it could possibly be! Just remember that the "co-ords" work in terms of (y, x) [ie. Row, Column] - but also remember that these are not co-ordinates, but indexes.] That said - you can still refer to the range as you specified above. Just make sure the colon goes INSIDE the quotes! [ie Range("B" & Start & ":B" & End) You are building a STRING reference to a range this way, so treat it like one!] I hope this isn't getting too confusing. If it is, here's a cheat snippet of code: Sub ExampleMacro() Dim MyRange As String MyRange = Cells(5, 2).Address & ":" & Cells(9, 2).Address Cells(1, 1).FormulaArray = "=SUM(ABS(" & MyRange & "))/COUNTA(" & MyRange & ")" End Sub This will put the average of absolutes in range B5:B9 in cell A1. It does it by defining the range as a string, then using that string to complete the text for the function. Tom