complex formula
"RENEE" wrote:
Tried that one out and it works perfectly.
I don't think so.
"Rick Rothstein" wrote:
=0.667*MIN(A1,2500)
+0.5*MIN(MIN(A1-2500,3500),6000)
+0.4*MAX(A1-6000,0)
The middle term looks suspicious. For any value in A1 less than 2500,
MIN(MIN(A1-2500,3500),6000) returns a negative number.
For example, test with A1=2000. The result should be 1334 (2000*66.7%).
Rick's formula returns 1084, viz. 2000*66.7% + (-500*50%).
We can correct Rick's formula, befitting his style, to wit:
=0.667*MIN(A1,2500)
+0.5*MAX(0,MIN(MIN(A1-2500,3500),6000))
+0.4*MAX(0,A1-6000)
But the MIN(MIN(...)) construct seems superfluous: the inner MIN is no more
than 3500, which is always less than 6000.
(Note that if 3500 were replaced with a larger number X, 6000 would also be
replaced with a larger number, 2500+X, which is always larger than X.)
So Rick's corrected formula can be simplified to:
=0.667*MIN(A1,2500)
+0.5*MAX(0,MIN(A1-2500,3500))
+0.4*MAX(0,A1-6000)
PS: You can continue to write 66.7%, 50% and 40% instead of the decimal
fraction equivalents.
----- original message -----
"RENEE" wrote:
Tried that one out and it works perfectly. Thanks so much!!!!
"Rick Rothstein" wrote:
Assuming your $7,417 value is in A1, I think this formula does what you
want...
=0.667*MIN(A1,2500)+0.5*MIN(MIN(A1-2500,3500),6000)+0.4*MAX(A1-6000,0)
--
Rick (MVP - Excel)
"RENEE" wrote in message
...
Could someone please!!! help me to set up a complex formula which I will
use
many times over.
I have an amount - say $7,417. I need to take 66.7% of the first $2,500
(which comes to $1,667.50). This leaves me with $4,917 of the original
amount and I need to take 50% of the next $3,500 ($1,750) - leaving me
with
$1,417 of the original number which I need to take 40% of ($566.80) Then,
I
add all the percentages up to come to $3,984.30. I have to do this a lot
and
could certainly use an easier way other than a calculator and a large
sheet
of paper. Thanks.
.
|