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?
This can be written as a single formula, a bit complicated.
Can you use helper columns? If so, the formula can be simplified and you can more easily have cells with the various rates so they could be changed as needed.
Undoubtly there must be a clever formula for that, but maybe something easy as this example can help you further?
(I hope you get to see the file, it's a bit strange how this uploading works)
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
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
