Averaging returns from Hlookup that include values and text

[hellcat83]

Hi

I'm in the process of building a sheet for the guys at work.

Part of the sheet requires me to take a range of cells, convert them using hlookup into either straight values or text and then average the resulting range.

Doing this as a two step process fine, but nesting the text returns from the hlookup into the average function is returning ###.

I've tried this every way I can think of, so I though someone might be able to help.

I've put a sample of the original data, converted data and latest formula below;


Original: 3a, 3c, -
Converted through hlookup on its own: 3.3, 3.1, -
Latest formula; =AVERAGE(IF(ISNUMBER(HLOOKUP(DK14,'Grade Boundaries'!$G$30:$AB$31,2,FALSE)),HLOOKUP(DK14,'Grade Boundaries'!$G$30:$AB$31,2,FALSE),""),IF(ISNUMBER(HLOOKUP(DM14,'Grade Boundaries'!$G$30:$AB$31,2,FALSE)),HLOOKUP(DM14,'Grade Boundaries'!$G$30:$AB$31,2,FALSE),""))

I've tried changing the "-" to a blank and I've tried using a range on the lookup to cut things down a bit, but to no avail.

Any help would be great.

Cheers

 

[AD_Taylor]

I've had a lot of problems with this as well.

I think what you're trying to do is say have a list of items (as text), use lookups to get those items into numbers and then average those numbers. And you're using nesting the formulas to get around having to have too many columns?

Unfortunately I don't think its going to work. When you use the AVERAGE function on a range, it seems to automatically know that "-"/"" are not numbers and automatically excludes them. When you use it as nested statement it explicitly includes all of the items so therefore includes the text values as well.

If you give me a few days I might be able to come back to you with a user defined (VBA) formula that can do it? I'd call it something like AverageHLookup or something like that

 

[hellcat83]

Hi

That's exactly what I'm trying to do. I actually tried testing it long hand, placing the data in separated by commas, and it seems that while average will exclude text if entered as a range, when specified it can't handle it.

Its not a massive problem for me to put in extra columns and hide them, I just wanted to be as neat as poss, but if you want to try something that would be good.

Cheers