Calculating Averages in Excel where a factor contains

[arentl]

Can anyone assist me in creating a formula where I can calculate an average when some of the factors contain < symbols? For example: what is the average of <5, <2 and .033.

 

[Hui]

Assuming your data is in C2:C4 which it surely wont be

The following array formula will give you what you want

=AVERAGE(IF(ISTEXT(C2:C4),VALUE(RIGHT(C2:C4,LEN(C2:C4)-1)),C2:C4))

Enter this with Ctrl Shift Enter

Gives me 2.344333333

Adjust range to suit


or slightly shorter

=AVERAGE(IF(ISTEXT(C2:C4),1*SUBSTITUTE(C2:C4,"<",""),C2:C4))

 

[arentl]

Thank you. This works perfectly. :0)


Lisa