Excel: метод расчета средних

Question

Я работаю над созданием приведенной ниже таблицы в Excel и пытаюсь найти лучший способ для вычисления значений Размер (944) / Аренда (2212) / Аренда PSF ($ 2,34), чтобы мне не нужно было изменять их формулы каждый раз, когда я добавляю новый тип Unit Mix.

Прямо сейчас моя формула для расчета среднего размера выглядит так:

=((F3*E3)+(F4*E4)+(F5*E5)+(F6*E6))/E8

Это суммирование количества * размера для каждой смеси единиц, деленное на общее количество единиц.

Вы можете видеть, как, если я добавлю еще одну единицу измерения в группу, мне придется изменить эту формулу.

Есть ли способ обойти это?

Answer 1

Вы можете использовать SUMPRODUCT():

=SUMPRODUCT(E3:E6*F3:F6)/E8

Это будет перебирать диапазоны, умножать каждую строку и суммировать результаты. Затем он делит сумму на E8.

Если то, что требуется, является полностью динамической формулой, тогда используйте это:

=SUMPRODUCT(D3:INDEX(D:D,MATCH("Market Residential",A:A,1)-1)*E3:INDEX(E:E,MATCH("Market Residential",A:A,1)-1))/INDEX(D:D,MATCH("Market Residential",A:A,1))

Он найдет строку, которая имеет Market Residential в столбце A, и будет использовать ее в качестве ссылки. Таким образом, при добавлении или удалении строк формула всегда будет содержать правильные диапазоны.

Хотя первый, если он не удаляет первый или последний, автоматически изменит размер диапазона с добавлением или удалением.

Answer 2

Вы можете просто использовать формулу массива для SUM

=SUM(E3:E6*F3:F6)

Это формула массива, поэтому после ее ввода вы должны нажать Ctrl Shft Entr

Это должно обновить, если вы сделаете изменение в середине диапазона.