meaning of this function

A

afdmello

MAX($B$2:$B$14)/$B$2:$B$14
I understand it as : find out the maximum value of the numbers in column B
row 2 to 14 and divided by ???


The whole formula as displayed in the formula bar is :

=SUMPRODUCT(D2:D14,MAX($B$2:$B$14)/$B$2:$B$14)


AFD
 
B

Bernie Deitrick

AFD,

Overall, that formula yields the same as this long mess:

D2*MAX(B2:B14)/B2 + D3*MAX(B2:B14)/B3 +...+ D14*MAX(B2:B14)/B14

HTH,
Bernie
MS Excel MVP
 
K

Ken Wright

No idea without seeing the data, but what is happening is that every value
in B2:B14 is being divided by the maximum value of B2:B14. That gives you a
new set of numbers that are then each multiplied by their corresponding
values in D2:D14, and the whole lot is added together.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :)
------------------------------­------------------------------­----------------
 
K

Ken Wright

Correction:-

The maximum value of B2:B14 is being divided by every value in B2:B14. That
gives you a new set of numbers that are then each multiplied by their
corresponding values in D2:D14, and the whole lot is added together.

Regards
Ken.............
 
B

Bernard Liengme

Let make the formula cover a smaller range to make explanation shorter:
=SUMPRODUCT(D2:D4,MAX($B$2:$B$4)/$B$2:$B$4) (I have changed 14 to
4)

Let B2:B4 have values 4, 2, 8
Let D2:D4 have values 3, 6, 9
MAX($B$2:$B$4) is 8, so the array MAX($B$2:$B$4)/$B$2:$B$4 is:
{8/4, 8/2, 8/8} or {2, 4, 1}
The array D2:D4 is {3,6,9}
SUMPRODUCT does this: (3 * 2) + (6 * 4) + (9 * 1) = 6 +24 +9 = 39

hope this helps
 
H

Harlan Grove

afdmello said:
The whole formula as displayed in the formula bar is :

=SUMPRODUCT(D2:D14,MAX($B$2:$B$14)/$B$2:$B$14)
Click to expand...

Which could be rewritten as

=SUMPRODUCT(D2:D14,1/$B$2:$B$14)*MAX($B$2:$B$14)
 
Top