I am trying to create an excel formula for the following scenario:

If the subtotal (ie E74) is 100,000 or less = 6% of E74. If the subtotal
is over 100,000, 6% on the first 100,000, 4% on the next 400,000 and 2% above
500,000.

Can anyone help? This is a bit out of my league.

Thanks!

Wutnik wrote...
....
If the subtotal (ie E74) is 100,000 or less = 6% of E74. If the subtotal
is over 100,000, 6% on the first 100,000, 4% on the next 400,000 and 2% above
500,000.
....

=6%*MIN(E74,100000)+4%*MAX(MIN(E74-100000,400000),0)+2%*MAX(E74-500000,0)

One way

=MIN(E74,100000)*6%+(MIN(E74-100000,300000))*4%+(MAX(0,E74-400000)*2%)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wutnik" wrote in message
...
I am trying to create an excel formula for the following scenario:

If the subtotal (ie E74) is 100,000 or less = 6% of E74. If the subtotal
is over 100,000, 6% on the first 100,000, 4% on the next 400,000 and 2%
above
500,000.

Can anyone help? This is a bit out of my league.

Thanks!

Thank you!!!! You just saved me hours trying to figure this out!!!!



"Harlan Grove" wrote:

Wutnik wrote...
....
If the subtotal (ie E74) is 100,000 or less = 6% of E74. If the subtotal
is over 100,000, 6% on the first 100,000, 4% on the next 400,000 and 2% above
500,000.
....

=6%*MIN(E74,100000)+4%*MAX(MIN(E74-100000,400000),0)+2%*MAX(E74-500000,0)

This just uses 1 Min function:

=MIN(12000+0.02*A1,2000+0.04*A1,0.06*A1)

HTH
--
Dana DeLouis
Win XP & Office 2003


"Wutnik" wrote in message
...
I am trying to create an excel formula for the following scenario:

If the subtotal (ie E74) is 100,000 or less = 6% of E74. If the subtotal
is over 100,000, 6% on the first 100,000, 4% on the next 400,000 and 2%
above
500,000.

Can anyone help? This is a bit out of my league.

Thanks!

Dana DeLouis wrote...
This just uses 1 Min function:

=MIN(12000+0.02*A1,2000+0.04*A1,0.06*A1)
....

Why refer to A1 3 times?

=MIN({12000,2000,0}+{0.02,0.04,0.06}*A1)

Bob Phillips wrote...
One way

=MIN(E74,100000)*6%+(MIN(E74-100000,300000))*4%+(MAX(0,E74-400000)*2%)
....

If E74 were blank, this returns 2000. Is that correct?

If E74 were zero, this returns -4000. Is that correct?

If E74 were 50000, this returns 1000. Is that correct?