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

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

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

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

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

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

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

=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.


--
goober
------------------------------------------------------------------------
goober's Profile: http://www.excelforum.com/member.php...o&userid=19838
View this thread: http://www.excelforum.com/showthread...hreadid=490230