Forum

Notifications
Clear all

Need formula for calculation of charges

5 Posts
3 Users
0 Reactions
76 Views
(@waqasmunir)
Posts: 21
Eminent Member
Topic starter
 

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

 
Posted : 15/12/2019 7:38 am
Anders Sehlstedt
(@sehlsan)
Posts: 970
Prominent Member
 

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.

 
Posted : 15/12/2019 12:31 pm
(@waqasmunir)
Posts: 21
Eminent Member
Topic starter
 

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.

 
Posted : 15/12/2019 4:32 pm
(@mynda)
Posts: 4761
Member Admin
 

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

 
Posted : 15/12/2019 5:42 pm
(@waqasmunir)
Posts: 21
Eminent Member
Topic starter
 

Thanks for your cooperation and your given formula resolve my query.

Waqas Munir

 
Posted : 15/12/2019 5:56 pm
Share: