Question description:
This user has given permission to use the problem statement for this
blog.
I need a formula that take a total amount, which is in cell c37, then times it by .015 and then adds 15. Then, add d2 and a5 and if the results show 400 or more, it will add .10 per every number 400 and above. Once it has done all that, I need it to times 1.2 for the VAT.
Solved by M. S. in 34 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
19/10/2017 - 06:55
HI, It's pleasure to help you today:)
User
19/10/2017 - 06:55
hi. u c describ
Excelchat Expert
19/10/2017 - 06:56
Please let me know, where the formula to be added?
User
19/10/2017 - 06:56
i dont understand
Excelchat Expert
19/10/2017 - 06:56
I have to write a formula. So, where should i place the formula
Excelchat Expert
19/10/2017 - 06:56
I will pick up the value from C37
User
19/10/2017 - 06:56
the sum it needs to times .015 is in c37
Excelchat Expert
19/10/2017 - 06:57
Shall i place the formula in E37
User
19/10/2017 - 06:57
ni, the fomula goes into b68
Excelchat Expert
19/10/2017 - 06:57
Ok. I will provide you the formula
Excelchat Expert
19/10/2017 - 06:57
You can copy it in your local file
User
19/10/2017 - 06:58
its for work and I have signed an agreement not to uplaod to any website
Excelchat Expert
19/10/2017 - 06:58
Ok. I got it.
Excelchat Expert
19/10/2017 - 06:58
I am working on the formula based on the information provided.
User
19/10/2017 - 06:58
thank you
Excelchat Expert
19/10/2017 - 06:58
=C37*0.15+15+D2+A5
Excelchat Expert
19/10/2017 - 06:58
t
Excelchat Expert
19/10/2017 - 06:59
Till A5 it is clear
Excelchat Expert
19/10/2017 - 06:59
Now, i need to know, the next part
Excelchat Expert
19/10/2017 - 06:59
if the results show 400 or more, it will add .10 per every number 400 and above.
Excelchat Expert
19/10/2017 - 06:59
The result after adding A5?
User
19/10/2017 - 07:00
=C37*0.15+15 is correct. The reason to add d2 and a5 is that if those two cells add up to 400 and more, then it is to add 10 pence per every transaction.
User
19/10/2017 - 07:00
d2 and a5 sybolizes transactions
Excelchat Expert
19/10/2017 - 07:00
Ok. I am writing for it
Excelchat Expert
19/10/2017 - 07:01
=if(C37*0.15+15+D2+A5<400,C37*0.15+15+D2+A5,(C37*0.15+15+D2+A5)+((C37*0.15+15+D2+A5)-400)*0.1)*1.2
Excelchat Expert
19/10/2017 - 07:01
Here is the formula. Please check
User
19/10/2017 - 07:01
k. one min]
User
19/10/2017 - 07:02
it is wrong
Excelchat Expert
19/10/2017 - 07:03
what is the value in C37 for testing
User
19/10/2017 - 07:03
the cost for gateway
User
19/10/2017 - 07:03
payment system
User
19/10/2017 - 07:03
let me explain
Excelchat Expert
19/10/2017 - 07:04
Ok. It is 0.015 and not 0.15
User
19/10/2017 - 07:04
the payment system cost 15 plus VAT a month. Then .015 for the total amount made (c37). Once we hit over 400 transations, then there are additional charges of .1 per trasations. D2 and A5 are the number of transations
Excelchat Expert
19/10/2017 - 07:05
I will update it. However, please provide me the C37 value for testing
User
19/10/2017 - 07:05
c37 changes everyday
Excelchat Expert
19/10/2017 - 07:05
Ok. For the present testing.
User
19/10/2017 - 07:05
5940.31
Excelchat Expert
19/10/2017 - 07:06
Ok. What is the expected result?
Excelchat Expert
19/10/2017 - 07:06
Is it 124.92558?
User
19/10/2017 - 07:06
no
User
19/10/2017 - 07:07
if the transations are 399, the result should be 89.10
Excelchat Expert
19/10/2017 - 07:08
result is C37 value
User
19/10/2017 - 07:08
I dont no how to explain it to you any better
Excelchat Expert
19/10/2017 - 07:10
Oh. Your messages were not updated earlier.
Excelchat Expert
19/10/2017 - 07:10
I am checking once again
User
19/10/2017 - 07:11
c37 has the value that is used for b68. It takes c37 and times it by .015. Then add 15. that is for the first 399 charges. Once it reaches 400 charges and over, it will then add .10 per charge. The charges are d2 and a5 added together. After it has done all that, it will add VAT of 20%(times 1.2)
Excelchat Expert
19/10/2017 - 07:13
Please take look at the preview sheet in D37 cell
Excelchat Expert
19/10/2017 - 07:14
What are the values in D2 and A5
User
19/10/2017 - 07:14
d37 has the first part.
User
19/10/2017 - 07:14
d2 and a5 change all the time
User
19/10/2017 - 07:14
just do d2+a5
Excelchat Expert
19/10/2017 - 07:15
Yes that is right. We have to have some values for testing it
User
19/10/2017 - 07:15
u choose the calue
User
19/10/2017 - 07:15
as long as it adds .1 after 400
User
19/10/2017 - 07:16
do not add a2 and d5 to c37*.15+15. It will give false results
User
19/10/2017 - 07:17
d2 and a5 are counting the transations and then seeing if there is a .1 charge for every transations
Excelchat Expert
19/10/2017 - 07:17
ok. when D2+A5 cross 400 then i need to add 0.1
User
19/10/2017 - 07:19
if the sum of d2 and a5 is 399, then c37*.015+15. If its over 399, include the results of c37*.015+15 and add .1 for every transations above 399. Then times results by 1.2
User
19/10/2017 - 07:19
do u understand
Excelchat Expert
19/10/2017 - 07:19
yes. I did that
User
19/10/2017 - 07:19
where
Excelchat Expert
19/10/2017 - 07:20
1.2 is for >399 or from 0 as well
Excelchat Expert
19/10/2017 - 07:20
D37 cell has formula in it
User
19/10/2017 - 07:20
1.2 is for total results
Excelchat Expert
19/10/2017 - 07:20
=if(D2+A5<400,C37*0.015+15,C37*0.015+15+(D2+A5-399)*0.1)*1.2
User
19/10/2017 - 07:21
let me test
User
19/10/2017 - 07:22
WRONG
User
19/10/2017 - 07:22
HOW R U NOT UNDERSTANDING THIS
Excelchat Expert
19/10/2017 - 07:23
Please check the false condition. I have applied the same logic. and adding 0.1 for each transaction >399
Excelchat Expert
19/10/2017 - 07:23
and the result is multiplied by 1.2
User
19/10/2017 - 07:24
TAKE THE SUM OF C37 AND TIMES IT BY .015, THEN ADD 15. BUT IF THE SUM OF D2 AND A5 IS HIGHER THAN 399 THE ADD .1 FOR EVERY TRANSACTION. THEN TIMES THE WHOLE THING BY 1.2
User
19/10/2017 - 07:24
its not that hard to understand
Excelchat Expert
19/10/2017 - 07:25
Can you please type the values in the preview for c37, D2,A5 as per your sheet. I am doing same thing. Please check the formula written.
Excelchat Expert
19/10/2017 - 07:26
What is C37 value
User
19/10/2017 - 07:26
look, =c37*.015+15;if(a2+d5>=400;*.1
Excelchat Expert
19/10/2017 - 07:26
Please type in that location
User
19/10/2017 - 07:26
something like that
Excelchat Expert
19/10/2017 - 07:28
C37*0.015+15+(D2+A5-400)*0.1
Excelchat Expert
19/10/2017 - 07:29
Please type in C37 cell and we can see the result in D37 in the preview
User
19/10/2017 - 07:29
WRONG
User
19/10/2017 - 07:29
Never mind. U cant understand
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
