# Average calculation of numbers with special characters

1. ## Average calculation of numbers with special characters

Hi All...

Wonder if someone could help me with this excel formula. I am trying to calculate the average for the values in two cells with numbers and special characters. The formula i am using is.

=SUMPRODUCT((SUBSTITUTE(SUBSTITUTE(D6:E9,"▼",""),"▲","")*1))/COUNTA(D6:E9)

it is coming up with #VALUE ERROR. And i can't get my head around. May be i am going through the thinking cramp in my head. Your help is much appreciated. I have also attached a sample file for you guys to have a look. Thanks in advance.  Register To Reply

2. ## Re: Average calculation of numbers with special characters

This formula will not work on your spreadsheet. You have to remove merged cells and cells with data should be one under the other without empty rows in between.  Register To Reply

3. ## Re: Average calculation of numbers with special characters

Thanks AlKey for pointing out the mistake i was making...much appreciated.  Register To Reply

4. ## Re: Average calculation of numbers with special characters

Why the need to merge cells? merged cells cause more problems than anything!!

If you put those 2 "values" in F6:F7, ,then use that same formula, you get an answer of 8.625
F6=10.00 ▲
F7=7.25 ▼
F8=8.625
F8=SUMPRODUCT((SUBSTITUTE(SUBSTITUTE(F6:F7,"▼",""),"▲","")*1))/COUNTA(F6:F7)  Register To Reply

5. ## Re: Average calculation of numbers with special characters

You can also use this formula for Average

=AVERAGE(VALUE(LEFT(F6:F7,LEN(F6:F7)-2)))

This must entered as an array formula with Ctrl+Shift+Enter

 F G 5 6 10.00 ▲ =AVERAGE(VALUE(LEFT(F6:F7,LEN(F6:F7)-2))) 7 7.25 ▼ 8 8.63 <---- in F8 9  Register To Reply

6. ## Re: Average calculation of numbers with special characters

The others make practical comments but if you need to keep the blank rows and cannot split the number then you will need a formula of the following sort
Formula:  `Please Login or Register  to view this content.`  Register To Reply