Question description:
This user has given permission to use the problem statement for this
blog.
how to apply incremental charges if any amount increased
Suppose A1 contains 175000 and after every increment of 50000 we have to charge $40. How do we do it
Solved by X. U. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
24/08/2018 - 09:44
Greetings! Thank you for choosing Got It Pro!
Excelchat Expert
24/08/2018 - 09:44
Can you share your worksheet please?
Excelchat Expert
24/08/2018 - 09:44
Or should we just make up an example data?
User
24/08/2018 - 09:45
ok
Excelchat Expert
24/08/2018 - 09:45
Okay to you sharing your sheet?
Excelchat Expert
24/08/2018 - 09:45
You can send it by clicking on the clip icon and then selecting your file and pressing enter.
User
24/08/2018 - 09:46
its not uploading
User
24/08/2018 - 09:46
file attached
[Uploaded an Excel file]
Excelchat Expert
24/08/2018 - 09:46
okay hold on
User
24/08/2018 - 09:46
wait I will be on call
User
24/08/2018 - 09:46
sorry I am on call
User
24/08/2018 - 09:46
pls
Excelchat Expert
24/08/2018 - 09:47
Okay, I will try to work on your sheet
Excelchat Expert
24/08/2018 - 09:48
Let me know when you're back.
User
24/08/2018 - 09:51
yes
User
24/08/2018 - 09:51
have you seen the sheet
Excelchat Expert
24/08/2018 - 09:51
Yes
Excelchat Expert
24/08/2018 - 09:51
What column would be our reference?
Excelchat Expert
24/08/2018 - 09:51
Is it column AR?
User
24/08/2018 - 09:51
you just unhide the sheet first
Excelchat Expert
24/08/2018 - 09:51
okay
User
24/08/2018 - 09:51
sorry columns
Excelchat Expert
24/08/2018 - 09:52
Done
Excelchat Expert
24/08/2018 - 09:52
Then?
User
24/08/2018 - 09:52
go to column L row 95
Excelchat Expert
24/08/2018 - 09:52
Sure, I'm here.
Excelchat Expert
24/08/2018 - 09:52
69,200
User
24/08/2018 - 09:52
yes
User
24/08/2018 - 09:53
in case this figure goes more than 175000
User
24/08/2018 - 09:53
and I have to charge $40 on each 50000 increment
User
24/08/2018 - 09:53
how do I do it
Excelchat Expert
24/08/2018 - 09:53
Where do we put the new value?
Excelchat Expert
24/08/2018 - 09:53
A separate column?
User
24/08/2018 - 09:53
you just show me where you are
User
24/08/2018 - 09:53
feel ok
User
24/08/2018 - 09:53
I will mange
Excelchat Expert
24/08/2018 - 09:54
Hold on, clarification.
Excelchat Expert
24/08/2018 - 09:54
If the value is say, 176000.
Excelchat Expert
24/08/2018 - 09:54
What would be the charge?
Excelchat Expert
24/08/2018 - 09:54
Is it $120?
User
24/08/2018 - 09:55
yes
Excelchat Expert
24/08/2018 - 09:55
Okay, cool.
User
24/08/2018 - 09:55
but how do I apply the formula to calculate
Excelchat Expert
24/08/2018 - 09:55
Yes hold on.
Excelchat Expert
24/08/2018 - 09:55
Do you see our open workbook here on the right side?
Excelchat Expert
24/08/2018 - 09:55
I'll make an example for you
User
24/08/2018 - 09:56
ok
Excelchat Expert
24/08/2018 - 09:56
There you go, please see cell B2.
Excelchat Expert
24/08/2018 - 09:57
=IF(A2>175000,ROUNDDOWN(A2/50000)*40,0)
Excelchat Expert
24/08/2018 - 09:57
This is the formula to get the total value that we would charge
User
24/08/2018 - 09:57
let me chck
User
24/08/2018 - 09:58
or I can put the A2>a2
User
24/08/2018 - 09:58
because it will be difficult to put each value of cell in the formula
User
24/08/2018 - 09:58
my sheet is holding data for whole moth
User
24/08/2018 - 09:58
month
Excelchat Expert
24/08/2018 - 09:59
Sorry, what is the question?
User
24/08/2018 - 10:00
I want to calculate the charges in separate sheet date-wise for the value more than 50000 and on every incremental of 50000 the 40 will be charged
User
24/08/2018 - 10:01
in case figure is 176000 then charges will be 160
User
24/08/2018 - 10:01
anything more than 50000 will be charged 40
Excelchat Expert
24/08/2018 - 10:01
Why would it be 160?
Excelchat Expert
24/08/2018 - 10:01
Should be only 120.
Excelchat Expert
24/08/2018 - 10:01
And yes, we already did that.
Excelchat Expert
24/08/2018 - 10:01
=IF(A2>175000,ROUNDDOWN(A2/50000)*40,0) this is the formula
Excelchat Expert
24/08/2018 - 10:02
You can just change the reference cell to where you need it applied
Excelchat Expert
24/08/2018 - 10:04
Thank you for choosing Got It Pro!
Excelchat Expert
24/08/2018 - 10:05
Have a great day!