Finding Average by Replacing Text Values with Numbers Using Array and Not Let()

user_1928597 0
Viewed 66

I have an excel sheet that looks like the following:

+-----------+------+-----------+------+----------------+
| Average   | Life | Age       | Life | Age            |
+-----------+------+-----------+------+----------------+
| Young     | Blah | Young     | Blah | Old            |
+-----------+------+-----------+------+----------------+
| Young     | Blah | Old       | Blah | Young          |
+-----------+------+-----------+------+----------------+
| Super Old | Blah | Super Old | Blah | Should Be Dead |
+-----------+------+-----------+------+----------------+

Consider the Average column - I want this to get the data from the Age columns (please note that age columns can exist anywhere in the sheet, the alternate representation above is merely for easy visualizing).

I want to encode Young = 0, Old = 1 ... Should Be Dead = 3 and then put an average based on range, like between 0 and 1 (included) = Young etc.

This is easily doable in VBA, but I was wondering is it even possible to do it using a formula?

Thanks!

excel-formula excel
1 Answers

In Microsoft365 you can create a nice re-usable variable through LET():

enter image description here

Formula in A2:

=LET(X,{"Young","Old","Super Old","Should Be Dead"},INDEX(X,MATCH(AVERAGE(MATCH(C2,X,0)-1,MATCH(E2,X,0)-1),{0,1,2,3})))

Where MATCH(C2,X,0)-1 (up to more than two arguments) is all these different columns you can refer to in your scenario.

Note that you don't really need LET() here, it's just .... handy.

Without LET():

=INDEX({"Young","Old","Super Old","Should Be Dead"},MATCH(AVERAGE(MATCH(C2,{"Young","Old","Super Old","Should Be Dead"},0)-1,MATCH(E2,{"Young","Old","Super Old","Should Be Dead"},0)-1),{0,1,2,3}))
user_97581940
Related