CODEWITHSUNDEEP
excelexcel-formula
Bort
Bort

Reputation: 551

How can I use functions like NUMBERVALUE() across a whole column/range in Excel?

I have a column of data that I want to get the average of. (F column below, GREEN)
Unfortunetely, each cell has the unit in it, thus I cannot simply use AVERAGE().

Before I continue, please note that I'm not asking for a better way of removing the units (That's a different question.)

Also note that in my included image, just below each formula is the formula in text.

My first attempt at getting the average was to make another column next to it using =LEFT(F,3) (Column L, RED) however trying to take the average of that failed (Column N, PINK), presumably because the result was a text string.

So I then tried using wrapping LEFT() in NUMBERVALUE(), giving me =AVERAGE(NUMBERVALUE(L17:L36)) but that just returns the that row's value (Column N, PURPLE)

Next I tried using doing the whole thing at once using =AVERAGE(NUMBERVALUE(LEFT(G17:G36,3))) (Column N, GREY) but that obviously wasn't going to work either.

Finally, I made a column using =NUMBERVALUE(LEFT(F36,3)) (Column G, BLUE) and then taking the AVERAGE() of that (Column I, GOLD).

That worked, but how can I use NUMBERVALUE() and LEFT() for a whole column? I want something like =AVERAGE(NUMBERVALUE(LEFT(G17:G36,3))) to work.

Example Spreadsheet

Upvotes: 0

Views: 3197

Answers (2)

zgirod
zgirod

Reputation: 4379

Your formula of =AVERAGE(NUMBERVALUE(LEFT(G17:G36,3))) is actually correct. However you are missing one step, you need to convert it to an array formula. After entering in the formula you need to make sure you hit Ctrl+Shift+Enter to tell excel you want to do an array formula or Command+Enter on a Mac.

That should do it for you.

Upvotes: 3

chris neilsen
chris neilsen

Reputation: 53126

The formula you suggested, entered as an Array Formula will work

=AVERAGE(NUMBERVALUE(LEFT(G17:G36,3)))

completed with Ctrl-Shift-Enter.

Upvotes: 1

Related Questions