Automatically filled

I have this spreadsheet and the formulas seem to work correctly, depending on the value of each column the column “F” is filled with the highest value. How to make the column “F” be filled automatically without dragging the formula from the previous cells?

Use an ArrayFormula…they’re magic!

I had thought about it, but I couldn’t make the formula, but it seems that this way it worked:



That looks correct!

Something is wrong, I saw that if the order of values
it is not sequential it does not respect the formula, it takes the value
of the sequence and not the bigest value, I imagine that some comparison is missing, as shown in the image below.

AH…you want max. I missed that. Tricky to do in array (max per row). I found this online…it shoudl work for you (replace A2:E with the range of values):

=query(transpose(query(transpose(A2:E),"Select "&REGEXREPLACE(join("",ArrayFormula(if(len(A2:A),"Max(Col"&ROW(A2:A)-ROW(A2)+1&"),",""))), ".\z","")&"")),"Select Col2")

I’ll try, it looks complicated

Ya…I only partly understand it myself…but it works!

I still haven’t managed to make it work, tomorrow I will continue.

Good morning Robert, I think I’m not making myself understood, maybe this formula doesn’t suit me, I don’t need the highest value in the matrix, but rather the highest value in a line that may or may not contain values.


The formula I sent is per line. Want to share your sheet with me and I’ll take a look?

no problem, give me your email.

Done. Forgot that commas and semicolons are reversed in formulas in other countries:

Robert, thank you so much I don’t know how to thank you,
you are the best, I have so much to learn.

Good morning Robert, sorry for bothering you, I thought I had solved my problem, but to my surprise when I went to put the formula in the work spreedsheet the result was not what was expected, did I do something wrong?
Thank you for your attention,

The formula only works if the columns containing the number values the are next to each other…spreading them out like you have it will break the formula (and really isn’t best practice for doing any sort of calculation).

thanks for the valuable information, sorry.

No worries. If you need further assistance, just let me know.

1 Like