ravinder_tig
06-01-2009, 08:50 PM
Hi Guys
I'm working on a project in which i had to calculate the average of particular field and that also with a macro in this application
i had done that that's working supperb but i'm coming accross a problem according to which the range which i had to take average
dosen't contains all integers
eg if range is A1:A10
then data is like
79
80
98
TBA
98
TBA
TBA
N/A
N/A
N/A
now ave for this range can't be calculated directly as many values are strings
what i need is
using a avg function on this range where TBA(To Be Anounced) is to be treated as 0 and N/A(Not Applicable) as null value
here's da avg dunction which i had used in macro
myColLett = Split(Columns(myCol).Address(False, False), ":")(0)
myFormula = "average(" & "'" & sourceSheetName & "'" & "!" _
& myColLett & startRow & ":" _
& myColLett & endRow & ")"
Where Start row and endrow are elements of a range which do find 1st and last row and my collett finds the corrosponding column alphabet
Your help would be gr8ly appriciated
i'd be really gr8ful for your help
Thanks n Regards,
Ravinder S
I'm working on a project in which i had to calculate the average of particular field and that also with a macro in this application
i had done that that's working supperb but i'm coming accross a problem according to which the range which i had to take average
dosen't contains all integers
eg if range is A1:A10
then data is like
79
80
98
TBA
98
TBA
TBA
N/A
N/A
N/A
now ave for this range can't be calculated directly as many values are strings
what i need is
using a avg function on this range where TBA(To Be Anounced) is to be treated as 0 and N/A(Not Applicable) as null value
here's da avg dunction which i had used in macro
myColLett = Split(Columns(myCol).Address(False, False), ":")(0)
myFormula = "average(" & "'" & sourceSheetName & "'" & "!" _
& myColLett & startRow & ":" _
& myColLett & endRow & ")"
Where Start row and endrow are elements of a range which do find 1st and last row and my collett finds the corrosponding column alphabet
Your help would be gr8ly appriciated
i'd be really gr8ful for your help
Thanks n Regards,
Ravinder S