June 25, 2016
Pls... Need help on below excel formula~
Example I went to travel 19 days and travel allowance base on this:
1-5 days - claim 40 per day
6-10 days - claim 50 per day
11-15 days- claim 60 per day
16-20 days - claim 70 per day
Answer is 40x5 days + 50x5days + 60x5days + 70x4days
1030 in total
Anyone hv the idea on this formula?
VIP
April 21, 2015
VIP
Trusted Members
June 25, 2016
You can choose the UDF way :
Function Allowance(NoOfDays)
Select Case NoOfDays
Case Is <= 5
Allowance = NoOfDays * 40
Case Is <= 10
Allowance = 200 + ((NoOfDays - 5) * 50)
Case Is <= 15
Allowance = 450 + ((NoOfDays - 10) * 60)
Case Else
Allowance = 750 + ((NoOfDays - 15) * 70)
End Select
End Function
or with a formula where A2 is the number of days
=IF(A2<=5,A2*40,IF(A2<=10,200+((A2-5)*50),IF(A2<=15,450+((A2-10)*60),(750+((A2-15)*70)))))
The assumption is that on the 16th day and there after, the rate is 70 per day
Sunny Kow
June 25, 2016
Thanks,I use if condition I never use UDF
SunnyKow said
You can choose the UDF way :Function Allowance(NoOfDays)
Select Case NoOfDays
Case Is <= 5
Allowance = NoOfDays * 40
Case Is <= 10
Allowance = 200 + ((NoOfDays - 5) * 50)
Case Is <= 15
Allowance = 450 + ((NoOfDays - 10) * 60)
Case Else
Allowance = 750 + ((NoOfDays - 15) * 70)
End SelectEnd Function
or with a formula where A2 is the number of days
=IF(A2<=5,A2*40,IF(A2<=10,200+((A2-5)*50),IF(A2<=15,450+((A2-10)*60),(750+((A2-15)*70)))))
The assumption is that on the 16th day and there after, the rate is 70 per day
Sunny Kow
1 Guest(s)