Formula HELP!! Please!

  • Thread starter Thread starter Emily
  • Start date Start date
E

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!
 
Thanks Geoff,

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

Thanks!!
Em
 
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})
 
J.E. McGimpsey said:
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})
Click to expand...

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