Forum Discussion
Tricky 3-Part Formula
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
6 Replies
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!
Nice formulas. Great utilization of SumProduct formulas. Especially like the second one.
Thx for your kind comment. Blessings!
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)) Matt,
That works PERFECTLY, and will make my life much easier!
I can't thank you enough!
All the best,
Dan
Daniel-
Glad to help out. Feel free to post back to the community if you need additional assistance!
