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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 02:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com