Formula HELP!! Please!

[Emily]

Hi,
I have a % (changes each month) ... ex: 104% which is
weighted in the following manner:

The first 60% is calc. with a multiplier of 0.60
The next 40% is calc. with a multiplier of 1.45
The next 4% is calc. with a multiplier of 30

Since the overall % changes each month, what is the best
formula to write so that I can drop in the % in one cell
and the formula will calc. the multiplier in another
cell. (So if I have 100% next month, I can leave the
formula and it will know not to calculate the 4%).

THANK YOU!

 

[GB]

Try this - assuming your %age is in A1

=0.6*A1 + 0.85*Max(0,A1-0.6)+ 28.55*Max(0,A1-1)

Geoff

 

[Em]

Thanks Geoff,

Can you tell me why it is 0.85 and 28.55? when the
multipliers are 1.45 and 30?

Thanks!!
Em

 

[J.E. McGimpsey]

Because Geoff's formula starts out applying 0.6 to the entire value
in A1, then adds the *difference* in multipliers to the parts of
A1's value above the threshold values (e.g., 0.6 + 0.85 = 1.45,
applied to the portion of A1 > 60%, and 0.6+0.85+28.55 = 30, applied
to the portion of A1 > 100%).

This does the same thing a bit more efficiently:

=SUMPRODUCT(--(A1>{0,0.6,1}),(A1-{0,0.6,1}),{0.6,0.85,28.55})

 

[GB]

Because Geoff's formula starts out applying 0.6 to the entire value
in A1, then adds the *difference* in multipliers to the parts of
A1's value above the threshold values (e.g., 0.6 + 0.85 = 1.45,
applied to the portion of A1 > 60%, and 0.6+0.85+28.55 = 30, applied
to the portion of A1 > 100%).

This does the same thing a bit more efficiently:

=SUMPRODUCT(--(A1>{0,0.6,1}),(A1-{0,0.6,1}),{0.6,0.85,28.55})

Clever - I was going to ask why the double minus? But then I saw that you
answered this a week ago:

'The explicit coercion by the double unary minus is necessary only
because SUMPRODUCT() expects numeric arrays, so a boolean array
throws an error.'

Now I have a clue, I find that help says: 'SUMPRODUCT treats array entries
that are not numeric as if they were zeros.' The help file could be just a
tad more explicit about boolean without becoming prolix. (Anyone from MS
listening?)

So, a boolean array is forced to its numeric values by prefacing it with --

I can replace -- and get the same effect with 1* or /1 but not with ++.
Where can I find out more, please?

Geoff

 

[J.E. McGimpsey]

Look at calculation operators in XL Help.

There you'll find that - can be a unary operator (and -- is just the
operator applied to the result of the first operation), but in
calculations, + is a binary operator - it needs two operands. For
compatibility purposes, I believe, the unary + doesn't throw a
syntax error, but rather is ignored.

As you've discovered, XL will attempt to coerce Booleans to 1/0 in
any calculation operation, so *1, /1, ^1, etc. will all work. The
most efficient, after --, is probably +0.