Special average calculation

  • Thread starter Thread starter Andrew Bourke
  • Start date Start date
A

Andrew Bourke

Hi
I would like to calculate the average of a list of numbers such as
45, 56e, 67, 32.
The e in 56e is important because it indicates an estimated score.

I could write a macro to strip the e away first, but I was wondering if
there was a simpler way to handle this, such as a VBA function or a
worksheet function ?

=AverageA doesn't seem to do it.

TIA

Andrew
 
=SUM(IF(A1:A10<>"",--SUBSTITUTE(A1:A10,"e","")))

this is an array formula, so commit with Ctrl-Shift-Enter

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Very nice.
Can this be put easily into a VBA macro ?
Rather than leave formulas in cells on the sheet I would like to do the
calculations through a macro.

TIA
Andrew
 
As an array formula you need to evaluate it, like so

Range("A1").Value =
Activesheet.Evaluate("=SUM(IF(A1:A10<>"""",--SUBSTITUTE(A1:A10,""e"","""")))")


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculation on a range problem 3
Calculating Test Score Averages 6
Moving Average of values within an array 2
Calculating Averages in VBA... 3
Calculating Averages 10
Insert text in column A and calculate the average from column B 4
Loop through all sheets, calculate Average in K1 of each sheet 9
calculating weighted average cost of capital in a macro 2

Back
Top