charges if 24 hour exceed and after every 24 hours charges will be different
1st 24 hour charges = 2000
2nd 24 hour charges = 2500
3rd 24 hour charges = 3000
4th 24 hour charges = 3500
i want to calculate charges for 130hours then answer should be 2000+2500+3000+3500=11000
Please download the attachment for further detail.
Waiting for reply and thanks in advance for help.
Regards
Waqas Munir
Hello,
Just a simple example attached where I am using VLOOKUP function. Check the highlighted cell. It is showing 96 but the underlaying value is 95.98, So you get correct charge.
Respected Anders Sehlstedt - Thanks for your reply but i know the solution with vlookup i m searching formula except vlook as i entered the value just for 7th 24 hour but it did not ended at 7th 24 hour it will continue i just make a chart of 1st 2nd and 3rd 24 hour charges to clear my question sorry but vlookup is not option when hours 200 or more than this so for vlook up i have to enter the criteria in range i dont need vlook up for this i have to update the table arry of vlookup. please suggest me a formula with works without this table or charges.
Hi Waqas,
You can use this array formula where Total Consumed Hours is in column C (enter with CTRL+SHIFT+ENTER):
=SUM(({24;48;72;96;120;144;168}<=$C6)*{2000;2500;3000;3500;3500;3500;3500})
Or if you have Office 365 you can use this dynamic array formula:
=SUM((SEQUENCE(7,1,24,24)<=$C6)*{2000;2500;3000;3500;3500;3500;3500})
Mynda
Thanks for your cooperation and your given formula resolve my query.
Waqas Munir