Conditional Formula Help Please!
 

I can get 2/3 of the formula done, but I'm stumped on the middle section.
Any help is greatly appreciated! I'm trying to calculate a benefit amount
based on earnings, as follows:

66.7% of the first $3,000 of monthly earnings
55% of the next $2,500 of monthly earnings
40% of any balance

The maximum result is $3,500.

Thanks to any and all!

Conditional Formula Help Please!
 

Assuming your monthly earnings is in A2:

=MIN(3500,MAX(0,(A2-5500))*40%+MIN(A2,3000)*66.7%+MIN(2500,MAX(0,A2-3000))*55%)

Note that the 5500 comes from the sum of your first two conditions. You
could of course replace the cutoff points and percentages with cell
references, if desired.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"CharlieGirl" wrote:

I can get 2/3 of the formula done, but I'm stumped on the middle section.
Any help is greatly appreciated! I'm trying to calculate a benefit amount
based on earnings, as follows:

66.7% of the first $3,000 of monthly earnings
55% of the next $2,500 of monthly earnings
40% of any balance

The maximum result is $3,500.

Thanks to any and all!

Conditional Formula Help Please!
 

=MAX(3500,IF(A15500,2001+1375+0.4*(A1-5500),IF(A13000,2001+0.55*(A1-3000),0.667*A1)))

Vaya con Dios,
Chuck, CABGx3




"CharlieGirl" wrote:

I can get 2/3 of the formula done, but I'm stumped on the middle section.
Any help is greatly appreciated! I'm trying to calculate a benefit amount
based on earnings, as follows:

66.7% of the first $3,000 of monthly earnings
55% of the next $2,500 of monthly earnings
40% of any balance

The maximum result is $3,500.

Thanks to any and all!

Conditional Formula Help Please!
 

Thank you! Exactly what I was hoping for!!

"Luke M" wrote:

Assuming your monthly earnings is in A2:

=MIN(3500,MAX(0,(A2-5500))*40%+MIN(A2,3000)*66.7%+MIN(2500,MAX(0,A2-3000))*55%)

Note that the 5500 comes from the sum of your first two conditions. You
could of course replace the cutoff points and percentages with cell
references, if desired.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"CharlieGirl" wrote:

I can get 2/3 of the formula done, but I'm stumped on the middle section.
Any help is greatly appreciated! I'm trying to calculate a benefit amount
based on earnings, as follows:

66.7% of the first $3,000 of monthly earnings
55% of the next $2,500 of monthly earnings
40% of any balance

The maximum result is $3,500.

Thanks to any and all!

Conditional Formula Help Please!
 

Sorry, my bad, shoulda been
=Min(3500,IF(A15500,2001+1375+0.4*(A1-5500),IF(A13000,2001+0.55*(A1-3000),0.667*A1)))

Vaya con Dios,
Chuck, CABGx3




"CLR" wrote:

=MAX(3500,IF(A15500,2001+1375+0.4*(A1-5500),IF(A13000,2001+0.55*(A1-3000),0.667*A1)))

Vaya con Dios,
Chuck, CABGx3




"CharlieGirl" wrote:

I can get 2/3 of the formula done, but I'm stumped on the middle section.
Any help is greatly appreciated! I'm trying to calculate a benefit amount
based on earnings, as follows:

66.7% of the first $3,000 of monthly earnings
55% of the next $2,500 of monthly earnings
40% of any balance

The maximum result is $3,500.

Thanks to any and all!