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