Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Special average calculation
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Special average calculation
=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) "Andrew Bourke" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Special average calculation
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 Bob Phillips wrote: =SUM(IF(A1:A10<"",--SUBSTITUTE(A1:A10,"e",""))) this is an array formula, so commit with Ctrl-Shift-Enter |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Special average calculation
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) "Andrew Bourke" wrote in message ... 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 Bob Phillips wrote: =SUM(IF(A1:A10<"",--SUBSTITUTE(A1:A10,"e",""))) this is an array formula, so commit with Ctrl-Shift-Enter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average Calculation | Excel Worksheet Functions | |||
Average IF with calculation | Excel Worksheet Functions | |||
use sub-totals and average in same calculation | Excel Worksheet Functions | |||
average calculation | Excel Discussion (Misc queries) | |||
Average Calculation | Excel Worksheet Functions |