multiple conditions

Joined
Aug 19, 2016
Messages
29
Reaction score
1
dear all

i have created a table as per attachment

trans(action column which is to be calculated on two conditions:
1, if the vendor column shows IRCTC the transaction charges would be
1.8% of the base amount if the amount is more than 10, otherwise 10.
2, if the vendor is not equal to IRCTC, transaction charges will be nil

Please help

m s narayanan
 

Attachments

  • msr credit card.xlsx
    12.1 KB · Views: 201
Joined
Aug 3, 2011
Messages
70
Reaction score
6
You could use an IF formula for this:

=IF(B6="IRCTC",MIN(F6*0.018,10),0)

This basically says that if cell B6 is equal to IRCTC, then the result is the lower of either 1.8% of the base amount (F6) or 10. If cell B6 is not equal to IRCTC then the result is 0.
 
Joined
Aug 19, 2016
Messages
29
Reaction score
1
You could use an IF formula for this:

=IF(B6="IRCTC",MIN(F6*0.018,10),0)

This basically says that if cell B6 is equal to IRCTC, then the result is the lower of either 1.8% of the base amount (F6) or 10. If cell B6 is not equal to IRCTC then the result is 0.
 
Joined
Aug 19, 2016
Messages
29
Reaction score
1
Thank you very much.
I shall try the formula suggested by you and let you know in due course.

M S Narayanan
 
Joined
Aug 19, 2016
Messages
29
Reaction score
1
Dmr becIky
i tried the formula but it returns 10/- in all the rows with vendor as "ÏRCTC"
i understand min formula selects minimum value from the list.
i am sorry i have not made my point clear..B
suppose the value is 400 the transaction row shows 5.40 which should be
actually 10 (being the minimum charges).
i tried the following formula and i want you please correct it;
=IF(B6="IRCTC" AND D6*.018>10,(D6*0,18,2),10)
your help please
m s narayanan
 
Joined
Aug 3, 2011
Messages
70
Reaction score
6
Ah ok, I think you need to use the MAX formula instead of MIN - my apologies!

=IF(B6="IRCTC",MAX(F6*0.018,10),0)
 
Joined
Aug 19, 2016
Messages
29
Reaction score
1
Thanks for your reply. I shall try it and let you know.
In the meantime. Can i add round function to the formula as below:
=IF(BY="IRCTC",ROUND(MAX(F6*0.018,10,2)),0)

Sorry for giving trouble to you.

M S Narayanan
 
Joined
Aug 3, 2011
Messages
70
Reaction score
6
You can, but you need to specify how you want the number rounded. It works like this:

ROUND(number to round, number of digits to round it to)

The second part of the formula is where you specify how you want the number rounded - 0 gives the nearest integer, 1 gives one decimal place, 2 gives two decimal places, etc.

Therefore this formula would round the calculation to the nearest integer:

=IF(B6="IRCTC",ROUND(MAX(F6*0.018,10,2),0),0)
 
Joined
Aug 19, 2016
Messages
29
Reaction score
1
Dear Mr Becky
Thank you very much for your guidance.
The formula really worked in the way I wanted.
M S Narayanan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top