Fill empty cells based on previous rolls, grouped by other field

Hi!

I'm trying to fill some blank cells with the following logic:

If the cell in [value] is blank, then search the rows with the same [key] find the mode of the [value] with the same [key], them, fill the blank with the mode.

I tried to use the multi row formula, with no success

Any help will be very much appreciated

regards.

data.xlsx

Preparation

Accepted answers

jamielaird

Hi @mauricio ,

Here's one approach that works:

Part of your logic means there were a few extra steps required here above what I expected - particularly how we handle null values.

Hopefully it all makes sense but let me know if you have any questions.

Impute Most Common Value.yxmd

All comments

jamielaird

Hi @mauricio ,

Here's one approach that works:

Part of your logic means there were a few extra steps required here above what I expected - particularly how we handle null values.

Hopefully it all makes sense but let me know if you have any questions.

Impute Most Common Value.yxmd

mbarone

The Imputation Tool on the Preparation palate should do it for you:
2021-06-08 14_17_55-Greenshot image editor.png

mauricio

Hi mbarone, thank you for the reply

Unfortunately the imputation tool cant group by values based on another field.

This solution appear to give a output different than the desired

mauricio

That will work just fine.

Thank you jamielaird

The solution is much bigger than I expected, but a can't see another way to solve this problem.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703