Alphanumeric averages

What happens where the Average is not whole - eg:

4c 4b 5b equates to 10 + 11 + 14 -> 35/3 -> 11.66..
edit: you're missing value for 4b (11) in your lookup_vector above

Would you wish to

a) round to nearest whole (eg 12 in this instance given .66)
b) always round up to nearest whole (eg 12)
or
c) always round down to nearest whole ? (eg 11)

The calc itself is relatively straightforward but the above will affect the minor details.

Assuming the values above are in the range of cells A1:A15, then perhaps this array formula may work for you.
{=AVERAGE(LEFT(A1:A15,1)+0)}

Numeric values sum to 45, divided by count of values(15) = 3

or if the numeric values will be greater than single digits and the alpha characters is always a single digit:

{=AVERAGE(LEFT(A1:A15,LEN(A1:A15)-1)+0)}

Note: array formula are committed by pressing Ctrl + Shift + Enter keys. Excel automatically adds the curly brackets.

Using the inline array approach:

Please Login or Register  to view this content.

As Palmetto outlines it would be better to store the various result options in a physical range.

The formula above does what you ask - you just need to alter reference to D4:F4 [taken from your first post] to be B4:D4 so as to pick up the grades in your file.

(what we're saying is - if you list the grades in a range from 5a to 1c in that order you can shorten the formula by removing the inline array and replacing with range references)

DO's formula converted to use strings, just for fun:

=MID(" 1c1b1a2c2b2a3c3b3a4c4b4a5c5b5a", MROUND(AVERAGE(SEARCH(D6:F6, " 1c1b1a2c2b2a3c3b3a4c4b4a5c5b5a")), 2), 2)

Must be confirmed with Ctrl+Shift+Enter.

Nice.

If you wanted to avoid Array entry then given the limited number of results you could encase the FIND results within an INDEX (0 return)

Would have, just looking for brevity.

That aside, do you opine that one is faster or superior to the other (i.e., w/ or w/o INDEX)?

I spoke with Colin Legg about it in the past and his point was simply that with large ranges an embedded INDEX approach will be slower than an Array equivalent given the fact that INDEX itself is a "relatively" slow function.
(it's still the best function in Excel in my opinion in terms of it's versatility)

If the number of items within the Array is limited I often use embedded INDEX to avoid need for CSE entry on the basis that for those asking for the solution I feel that the additional "risk" of CSE is not worth the hassle (edit - reset etc...)

Where do you stand on it ?

with large ranges an embedded INDEX approach will be slower ...

That's what I'd have figured, particularly from the fact that it adds an unnecessary function.

... given the fact that INDEX itself is a "relatively" slow function

That surprises me, but ... compared to what? Not OFFSET, in this usage.

"risk" of CSE is not worth the hassle

Concur, except that that that use of INDEX is obscure in what it does functionally (which is nothing, sort of, here). I think I only learned of that use in the last few years.

For the OP looking for a black box, it's a good thing I reckon.

Originally Posted by shg

...compared to what? Not OFFSET, in this usage.

Unfortunately I don't have any empirical proof etc I'm just gas bagging - repeating stuff I've picked up here and there from others.
I've always been led to believe that INDEX is slow and OFFSET fast - unfortunately 99% of the time OFFSET also means Volatile.

I believe in many respects the discussion of INDEX goes back to the thread yesterday where we were discussing which functions use just the Used Range / Precedent intersect.
I believe that the INDEX function used in this way is one of the "exceptions" and is iterative by nature (if not always)

Not sure this is a good example but consider:

A1:A10000 contain either x or y
B1:B10000 numbers
(ignoring SUMIF etc etc)

Please Login or Register  to view this content.

will be quicker (I'd say significantly so in relative % terms) than

Please Login or Register  to view this content.

This difference implies to me that INDEX used in this manner does not restrict itself to the Used Range / Precedent Intersect.

If we assume that to be the case then it follows there is zero value in using an embedded INDEX to circumvent an Array on performance grounds.
The additional overhead of the INDEX means it will in fact be slower.

Personally speaking it is the size of the precedent range (and therefore no. of iterations) that determine whether or not I insert the INDEX to circumvent the CSE entry requirement - that in conjunction with the user etc.
As you say this is obviously a subjective choice - mine are often misguided

Grateful for any additional insight.

unfortunately 99% of the time OFFSET also means Volatile

What's the other 1%?

Grateful for any additional insight.

I have none. It would be a generous gift if someone on the Excel team wrote a book on Excel internals. I understand the reasons why it won't happen**; as it is, we try to tease out information like trying to diagnose a mainframe problem with a stethoscope.


**Not to mention that only 117 people would buy it.

Originally Posted by shg

What's the other 1%?

Well, this is something that came up the other day in a discussion with JB re: Data Validation.

In Excel 2010 I've discovered that MS removed the ability to use :INDEX construct in Data Validation Source formulae.
(no one seems to know why (Rory has asked in the MS MVP forum))

This raised the question of how to best construct dynamic ranges for use with Data Validation in XL2010.

You can still create the same :INDEX range as a Dynamic Named Range and use the Name in the DV Source but you could also use OFFSET in DV Source directly.

You know me - I hate to see a Volatile but JB kindly referenced a link in which a poster asserted (with some logic!) that OFFSET in the context of Data Validation is not really Volatile given the DV Source formula is only evaluated when the DV cell is itself activated.
Given the cell contains only a constant thereafter there is no Volatility knock-on.
(this seems to make sense to me - a sheet whose only formulae reside within DV Source would never invoke the Calculate event)

That is a very interesting 1%. Thanks to JB, and thanks to you for passing it along.

how about this array formula :-

{=ROUND(AVERAGE((FIND(D4:F4,"1c1b1a2c2b2a3c3b3a4c4b4a5c5b5a")+1)/2),0)}

or if there are always 3 :-
{=SUM((LEFT(D4:F4,1)-1)*3+(CODE(UPPER(RIGHT(D4:F4,1)))-65),1)/3}

if the ammount varies

{=SUM((LEFT(D4:F4,1)-1)*3+(CODE(UPPER(RIGHT(D4:F4,1)))-65),1)/count(D4:F4)}

both are array formulas CSE entered

@squiggler47 - none of the above are returning the associated grade.

shg's formula already does this (efficiently).

Sorry I missed that he wanted the result as a string rather than a number, as an alternative this looses the CSE at the expense of adding a reference.

=MID("1c1b1a2c2b2a3c3b3a4c4b4a5c5b5a",1+2*ROUND(SUMPRODUCT(((LEFT(D7:F7,1)-1)*3+67-CODE(UPPER(RIGHT(D7:F7,1))))*1/3),0),2)

If you wanted to circumvent CSE entry then you could just use an embedded INDEX with shg's suggestion

=MID(" 1c1b1a2c2b2a3c3b3a4c4b4a5c5b5a", MROUND(AVERAGE(INDEX(SEARCH(D6:F6, " 1c1b1a2c2b2a3c3b3a4c4b4a5c5b5a"),0)), 2), 2)

The circumvention of Array was the basis of the thread from post 9 onwards.

We would seem to be going in circles a little.

Great thread guys. Really informative. I am currently teaching myself the index function and index and match so this has been good.
Hope you have all had a good weekend. Monday tomorrow
Darren Chapman