# Excel - IF Function Problem - Expert Solution

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
User 19/10/2017 - 06:55
hi. u c describ
Excelchat Expert 19/10/2017 - 06:56
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
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
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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.