**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.*