How to find the largest product of an array of values?

[ryesworld]

Hi, I have a spreadsheet with several hundred rows and multiple columns.
Each column has a multiplier value. Each row has numbers in some of the
columns. I need a formula that wil tell me what the maximum product would be
for each row of all the column multipliers and numbers.

eg. The multipliers are in Row 1 and the data table is B3:F6. The
resulting max product for each row is shown in column A.

A B C D E F
1 Multiplier 80 40 20 10 5
2 MAX PROD
3 40 0 1 1 0 0
4 160 2 1 1 1 1
5 100 1 1 5 1 10
6 100 1 1 1 1 20

I know the following would work and could be copied down for each row, but
it will become a very long formula with more colums:
=MAX(B$1*B3,C$1*C3,D$1*D3,E$1*E3,F$1*F3)
Could this formula be reworked to use an array, or is there another formula
that would be better?

Thanks,
Ryan

 

[vezerid]

Ryan,
array formulas can help here. For example, =MAX(B$1:F$1*B3:F3), entered
with Shift+Ctrl+Enter would do your job.

HTH
Kostis Vezerides

 

[John Michl]

Use an array formula in column A such as:
{=MAX($B$1:$F$1*B2:F2)} using Ctrl-Shift-Enter to enter the formula.
Don't add the braces. They will be automatically entered when you use
Ctrl-Shift-Enter.

- John

 

[goober]

=MAX($B$1:$J$1*$B3:$J3)

Try putting this formula in A3 then copy it down as far as you need.
It uses an array so after typing it in you have to press
ctrl+shft+enter to activate it.

Hope it helps.