sum function with a break point

**jspinx** · 01-02-2009, 06:59 PM

Hi Guys
I have a forumula I need to figure out. I am using EXCEL 2003

Lets say I charge a customer for the amount of widgets he takes per month. He gets charged $125 per widget up to 90 widgets. Then for each widget after that, he gets charged a reduced rate of $55 per widget.

Is it possible to have this all in one forumula?

Thanks

**Ron Coderre** · 01-02-2009, 07:07 PM

With
A1: (a number widgets.....eg 91)

This formula charges $125 for up to 90 widgets, then charges $55 for the remainder

Please Login or Register  to view this content.

In the above example, the formula returns: 11,305
(90*125+1*55)

Is that something you can work with?

**jspinx** · 01-02-2009, 07:13 PM

THanks Ron
where does the 70 come from in that equation?

**Ron Coderre** · 01-02-2009, 07:17 PM

Re: =A1*55+70*MIN(+A1,90)

It works this way....
Every widget is charged at $55.
Up to 90 widgets are charged at an additional $70 (125 - 55)

Does that help?

**jspinx** · 01-02-2009, 07:22 PM

yes, you are a genius. this worked, thank you

**VBA Noob** · 01-02-2009, 07:25 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save