multiple conditions

[msnarayanan]

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

 

[Becky]

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.

 

[msnarayanan]

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.

 

[msnarayanan]

Thank you very much.
I shall try the formula suggested by you and let you know in due course.

M S Narayanan

 

[msnarayanan]

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

 

[Becky]

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)

 

[msnarayanan]

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

 

[Becky]

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)

 

[msnarayanan]

Dear Mr Becky
Thank you very much for your guidance.
The formula really worked in the way I wanted.
M S Narayanan