allowances calculation

[revanthof92]

Dear Friends, pls help me with this problem in excel, this is my homework question. (to be solved in excel)

The question is to calculate the Travel allowance based on the conditions given below,

40% of the basic salary whose basic salary is up to $8000, subject to a minimum of $3000 or 25% of the basic salary whose basic salary is above or equal to $10000, subject to the maximum of $8000.

I'm stuck at the 'subject to minimum and maximum' clause..

Pls help me..

Thank you

 

[skorpionkz]

what is in case when salary is between 8000 and 10000?

 

[revanthof92]

what is in case when salary is between 8000 and 10000?

ok then, correct that 8000 to 10000

 

[skorpionkz]

change what? <8k or >10k?

If you change <8k then you would always get 3k allowance (if salary will be between 8 and 10k)

if you change >10k then you will never each 8k allowance as your max allowance would be 2.5k (if salary will be between 8 and 10k)


The only way is to use >10 or <=10k

=IF(A1<10000,IF(A1*0.4>3000,3000,A1*0.4),IF(A1>=10000,IF(A1*0.25>8000,8000,A1*0.25),""))