meaning of this function

[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(D214,MAX($B$2:$B$14)/$B$2:$B$14)


AFD

 

[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

 

[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 D214, 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
------------------------------­------------------------------­----------------

 

[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 D214, and the whole lot is added together.

Regards
Ken.............

 

[Bernard Liengme]

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

Let B2:B4 have values 4, 2, 8
Let D24 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 D24 is {3,6,9}
SUMPRODUCT does this: (3 * 2) + (6 * 4) + (9 * 1) = 6 +24 +9 = 39

hope this helps

 

[Harlan Grove]

The whole formula as displayed in the formula bar is :

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

Which could be rewritten as

=SUMPRODUCT(D214,1/$B$2:$B$14)*MAX($B$2:$B$14)

 

[afdmello]

Thank you all.

Now is understood what the $b$2: $b$14 stood for

AFD