PDA

View Full Version : Solved: averaging variable sized ranges



divingdingo
11-21-2007, 07:01 AM
back to wind data !!

if i want to average a series of wind speeds, then i can write something like this:

ActiveCell.FormulaR1C1 = "=AVERAGE(R[-1]C[-2]:R[31833]C[-2])

which averages the numbers in whichever column i happen to select relative to the activecell.

however, the ranges i'm dealing with change length. some arrays have 31833 rows in (as above) however others have twice that.

i've been learning something in the last week as i can now set a variable which can hold the number of rows that is in an usedrange. my promlem is that i'm having trouble writing the correct code to do the above average but using a variable instead of a number.


this is what i have tried, but none work

ActiveCell.FormulaR1C1 = "=AVERAGE(R[-2]C[-19]:R[CStr (i)]C[-19])


where i is the variable.

could anyone kindly suggest the correct way of writing this in VBA

mark

rory
11-21-2007, 07:23 AM
You can use:
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-2]C[-19]:R[" & i & "]C[-19])"


PS I assume i is in fact declared as a Long and not an Integer?

divingdingo
11-21-2007, 08:44 AM
thank you rory.

i'm still having problems with writing it in VBA without the dreaded yellow highlighter coming out and stopping the procedure!

i'm trying to assign the variable fiftyA to equal the average on a different sheet.

this is my code, which is oh so wrong!

fiftyA = AVERAGE(sheet1!R[-1]C[-3]:R[" & m & "]C[-3])

where m is a variable as LONG and Sheet1 is weher all the data is.

any ideas?

mark

rory
11-21-2007, 09:01 AM
You can't just put worksheet formulas into code like that. You can use:
fiftyA = Application.Average(range)
but it's not clear to me from your example what actual range you want to average - is it relative to the actual cell or is it a specific location?

divingdingo
11-21-2007, 09:06 AM
You can't just put worksheet formulas into code like that. You
damn it, so that's why it wasn't working too well! :)

so it looks like i need to set a range from sheet1, then average that range using the above code.

i'll try that, thanks

mark

divingdingo
11-21-2007, 09:27 AM
okay, so i'm still going round in circles!!

this is the bits of my code so far which relates to what i'm trying to do



Range("C2:C" & m).Name = "fifty"'this sets the name of the range

fiftyA = Application.Average("fifty") 'this is adapted from you above

Range("b2").Select
ActiveCell.FormulaR1C1 = fiftyA 'this is the output cell


not sure what i'm doing wrong. if you have any other ideas why i can't work this out (other than i'm a VBA debutant!!)

thank you for your help

Bob Phillips
11-21-2007, 10:26 AM
Range("C2:C" & m).Name = "fifty" 'this sets the name of the range

fiftyA = Application.Average(Range("fifty")) 'this is adapted from you above

Range("b2").Value = fiftyA 'this is the output cell

rory
11-21-2007, 10:26 AM
If you want it entered as a formula, you can use:
Range("B2").FormulaR1C1 = "=AVERAGE(sheet1!R[-1]C[-3]:R[" & m & "]C[-3])"

divingdingo
11-22-2007, 04:00 AM
fiftyA = Application.Average(Range("fifty")) 'this is adapted from you above



i can't believe i was one set of brackets out !! vba can be very frustrating sometimes.

thank you for helping me find the solutions to my problems and taking your time to do so.

:bow:

Bob Phillips
11-22-2007, 05:00 AM
You also missed Range!


__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

divingdingo
11-22-2007, 05:05 AM
You also missed Range!



opps, so i did !! and for a moment there i had my vba confidence up...!:rotlaugh:

thanks for noticing that i'm worse than i thought!

Bob Phillips
11-22-2007, 07:04 AM
That's quite alright it's my job.