Average between 2 rows (IF, Find)

[Ximena_Duray]

Hi everyone :grimacing: !

I have been working on different ways to represents performance information. We usually have to register how many points an employee got per week, and then we have to see the average of points by a pair of weeks :nerd_face: .

For example, if “Employee 1” got 100 points on week 10 and 100 points on week 11, and the corresponding pair of weeks is week 10 and week 11, I would like to see the average of points of week 10 and week 11 in the same column Biweekly Points (even if by row is repeated).

Here is the base I made:

Right now, what I tried was to use Find and IF this way :face_with_monocle: :

IF(FIND(Week,Biweek),AVERAGE({Points}),“NA”)

But the problem is that this formula doesn´t show the average unless:

**Biweek and Week have exactly the same information (for example both have W10-W11)* :fearful:

*If Biweek and Week are both text columns :fearful:

If I let Week column as a link and Biweek as a lookup of Week, the result of the formula is “NA” :weary:

I would like to keep Week and Biweek type of column but I am not sure if its possible to obtain the average the way I was thinking :sweat:

Any ideas would be very much appreciated!

[Justin_Barrett · Accepted solution]

I believe the problem is coming from your lookup field. Lookup fields, in most cases, return an array, even if they’re only pulling from a single linked record. Unfortunately, Airtable doesn’t automatically take the contents and turn it into a string when a formula field asks for it, so you have to do the conversion manually in your formula. The easiest way to do this is to concatenate the lookup field with an empty string: {Field Name} & ""

Going back to your first example, where the linked week is just named “W10”, try this formula:

IF(FIND(Week, Biweek & ""), AVERAGE({Points}), "NA")

This should work because “W10” is in “W10-W11”. However, in your later example, you changed the week name to “20-W10”, which is not found in “W10-W11”. Airtable only looks for an exact match. It’s not going to see that the “W10” part at the end of “20-W10” is in the other string. It’s all or nothing.

[Ximena_Duray]

@Justin_Barrett Thank you so much! This totally worked :star_struck: :star2: