meaning of this function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

afdmello - 26 Nov 2005 20:03 GMT

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

Reply to this Message

Bernie Deitrick - 26 Nov 2005 22:05 GMT

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

> MAX($B$2:$B$14)/$B$2:$B$14
> I understand it as : find out the maximum value of the numbers in column B
[quoted text clipped - 5 lines]
>
> AFD

Reply to this Message

Ken Wright - 26 Nov 2005 22:07 GMT

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.

Signature

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

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

> MAX($B$2:$B$14)/$B$2:$B$14
> I understand it as : find out the maximum value of the numbers in column B
[quoted text clipped - 5 lines]
>
> AFD

Reply to this Message

Ken Wright - 26 Nov 2005 22:11 GMT

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

> 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
[quoted text clipped - 11 lines]
>>
>> AFD

Reply to this Message

Bernard Liengme - 27 Nov 2005 02:14 GMT

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

Signature

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

> MAX($B$2:$B$14)/$B$2:$B$14
> I understand it as : find out the maximum value of the numbers in column B
[quoted text clipped - 5 lines]
>
> AFD

Reply to this Message

Harlan Grove - 27 Nov 2005 09:13 GMT

"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)

Reply to this Message

afdmello - 28 Nov 2005 05:50 GMT

Thank you all.

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

AFD

Reply to this Message