Tricky 3-Part Formula

[DANIEL LUCAS]

Tricky for me!

Aircraft flight time invoice.

I need to calculate monthly totals based on the following:

~First 5 hours (0.1 - 5.0) at $2000/hr.

~Next 5 hours (5.1 - 10) at $2500/hr

~Over 10 hours at $3000/hr.

So if there were 10.4 hours flown in a month, I need to calculate:

5.0*2000+5.0*2500+0.4*3000 ... automatically.

Thanks for the help!

Dan

[#2]

Try using the If Formula2 Example  (See attached file for reference):

=IF(B2<=5,B2*2000,IF(B2<=10,10000+(B2-5)*2500,22500+(B2-10)*3000))

[#3]

Matt,

That works PERFECTLY, and will make my life much easier!

I can't thank you enough!

All the best,

Dan

[#4]

Daniel-

Glad to help out.  Feel free to post back to the community if you need additional assistance!

[#5]

Hi to both!

I Leave two more options (in Matt's file).  One with helper range, an another without it.

Helper Range --> J1:K4 ( {"Hours","Value";0,2000;5,2500;10,3000} )

Formula with helper range:

[G2]: =SUMPRODUCT((B2>J$2:J$4)*(B2-J$2:J$4)*(K$2:K$4-N(+K$1:K$3)))

Formula without helper:

[H2]: =SUMPRODUCT((B2>{0;5;10})*(B2-{0;5;10})*{2000;500;500})

The advantage of these methods is that they allow you to handle multiple cumulative ranges without the need to lengthen the formula too much for each condition.  Check file. Blessings!

[#6]

John Jairo Vergara Domínguez-

Nice formulas.  Great utilization of SumProduct formulas.  Especially like the second one.  

[#7]

Thx for your kind comment.  Blessings!