formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query
This may be too specific of a formula, but I'm hoping there is a function or script that will accomplish it.
I need a Google Sheet to identify the largest number in a row and divide all of the OTHER numbers by 2, then add the entire row together.
Example: Row 1 has the following numbers… 100, 200, and 300
I would like the sheet to identify 300 as the largest number, divide 100 & 200 in half (50 & 100), then total the whole row together to get 450.
Is this possible?
You can do this with a combination of the INDIRECT and MATCH functions.
In the last column of your table add the following formula in line with the data that you would like to determine the max of:
=INDIRECT(ADDRESS([Name_Row], MATCH(MAX([Data_Span]), [Data_Span], 0) + [Column_Offset]))
=INDIRECT(ADDRESS([Name_Row], MATCH(MAX([Data_Span]), [Data_Span]) + [Column_Offset]))
Where [Name_Row] is the index of the row where your projector names are (e.g. 1 if the projector names are in row 1), [Data_Span] is the span of values that you're trying to find the max of, and [Column_Offset] is the distance of your first data value from column A (e.g. 1 if your data starts in column B).
See picture 
.
Best Answer