Log In
Register
Home
Active Member
July 23, 2024
Offline
Can I get a formula for excel sheet to create a simple travel allowance form??
If I type the Km the Amount column should be filled automatically
the arrangement should be like this, upto 2.5 Km =10, for 5Km= 13, for 7.5 Km=15, for 10 Km = 17
Help me plz
See Answer
Moderators
January 31, 2022
Offline
Best to create a small lookup table with the rates and 'from KM' limits. You can then use the LOOKUP function to retrieve the correct rate/allowance per KM. I've attached an example.
Answers Post
Active Member
July 23, 2024
Offline
Sir
plz see the attachment and give me a best example
Moderators
January 31, 2022
Offline
A screenshot isn't really helpful. Especially as it doesn't include the column and row headers. Better to upload your file. When you do that, please don't forget to press "Start upload" before you submit your reply.
Active Member
July 23, 2024
Offline
Sir
the distance and amount column is to be filled. If I enter a value in distance cell then the amount should automatically show the amount
Moderators
January 31, 2022
Offline
OK. See attached. I included your form in the file I sent earlier. See if you can get it to work.
Active Member
July 23, 2024
Offline
Sir
Your example that send already is multiplying and is not the actual amount that I want. For eg. when I type 2 in the distance cell then the amount column should be 10, And as per the sheet I've attached
Moderators
January 31, 2022
Offline
I misunderstood and though the allowance would be per KM. If that's not the case, just remove the multiplication bit of the formula. That would then be:
=LOOKUP(E4,Table1[KM],Table1[Rate])
Active Member
July 23, 2024
Offline
Thank you sir but there is another problem is there. 0-2.5 is 10, 2.5-5 is 13, 5-7.5 is 15. so plz correct me sir
Moderators
January 31, 2022
Offline
OK, My bad! I didn't read the 'upto' in your initial question.
See attached. And just understand, this work for upto the limit in the KM column. Not upto and including. If that's what you need, please change the limits in the lookup table.
1 Guest(s)
