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(D2:D14,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



"afdmello" <janrich@anywhere.com> wrote in message
news:uMG1%23Rs8FHA.2616@TK2MSFTNGP15.phx.gbl...
> 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
>
>

[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 :-)
------------------------------*------------------------------*----------------



"afdmello" <janrich@anywhere.com> wrote in message
news:uMG1%23Rs8FHA.2616@TK2MSFTNGP15.phx.gbl...
> 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
>
>

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


"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:uzrJAXt8FHA.1864@TK2MSFTNGP12.phx.gbl...
> 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 :-)
> ------------------------------*------------------------------*----------------
>
>
>
> "afdmello" <janrich@anywhere.com> wrote in message
> news:uMG1%23Rs8FHA.2616@TK2MSFTNGP15.phx.gbl...
>> 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
>>
>>
>
>

[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


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"afdmello" <janrich@anywhere.com> wrote in message
news:uMG1%23Rs8FHA.2616@TK2MSFTNGP15.phx.gbl...
> 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
>
>

[Harlan Grove]

"afdmello" <janrich@anywhere.com> wrote...
....
>The whole formula as displayed in the formula bar is :
>
>=SUMPRODUCT(D2:D14,MAX($B$2:$B$14)/$B$2:$B$14)

Which could be rewritten as

=SUMPRODUCT(D2:D14,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