LinkBack URL
About LinkBacks
I am thinking of using INDEX/MATCH or VLOOKUP to solve this question but I'm not sure how to go about doing it. Please advise. Thank you.
Attachment 692834
This is what I currently have.
Attachment 692835
I am thinking of using INDEX/MATCH or VLOOKUP to solve this question but I'm not sure how to go about doing it. Please advise. Thank you.
Attachment 692834
This is what I currently have.
Attachment 692835
Last edited by onewishtobegranted; 08-30-2020 at 10:42 AM.
Welcome to the forum.
There are instructions at the top of the page explaining how to attach your sample workbook so you can show us your data and what you have tried.
Nothing is more frustrating than to be working on homework and to get stuck. Without guidance, you might have no hope of finishing the assignment before the deadline. The Internet can be a big help in that respect, but without learning how to get the right answer, asking others to solve your homework questions for you simply becomes a downward spiral (and it's cheating).
We do not want to contribute to you cheating yourself out of your education, but we also acknowledge that seeking assistance to learn a concept is a legitimate request.
If you are genuinely interested in receiving help in the form of tutoring or coaching, then please rephrase the title of your original post to clearly indicate you are seeking coaching or tutoring help. Any forum members (who are willing to assist as a tutor) will modify their responses accordingly to facilitate your learning. Tutors don't tell you the answers, they help you figure it out for yourself; so don't expect answers, expect suggestions, or just plain hints. Also, be specific in describing the function/formula or technique you trying to learn, and tell us what you have attempted so far. Otherwise, expect your plea for homework answers to be ignored.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Something like this?
Try to genarate Rate table
Contruct a formula
Then uoload a samle worksheet to see what we can assist more.
Quang PT
I have a sample worksheet attached above named TAXI.xlsx, is that format not applicable?
You have only just attached the workbook.
What you have given us is a table. There are no workings, no attempted formulae. We need to see what you have done already to try to resolve this, then we can guide you and help you to correct your mistakes.
We are not going to just do it for you - sorry.
I agree with Ali. I am not even sure what the boundaries are,
If your first km is a standing rate and then you get billed every 400m or part there of to 10 km, the 9km do not divide evenly by 400m. So how do you wish to treat this.
I would look at some tax examples where you get charged differing amounts for each band of your income. It will be similar to this once you have matched the type of vehicle first.
Show us your attempt and we can attempt to guide you
Hi, sorry for that. I admit it seems like I'm digging for answers.
I have tried with vlookup and index+match but there seems to always been an error when I use vlookup. I'm not very sure if I have utilised my index+match formula correctly, if there are any improvements, please suggest. thank u!
Attached below is my updated version of the excel worksheet.
removed attachment < not sure of how to upload it properly
Last edited by onewishtobegranted; 08-28-2020 at 10:40 AM.
OK - now we are getting somewhere.
Explain in WORDS what you are trying to do - step-by-step - with this formula:
=SUM(VLOOKUP($B9,$A$1:$H$4,2,FALSE),VLOOKUP($B9,$A$1:$H$4,3,FALSE)*ROUNDUP((MIN($D$3,$C9)-1)/$D$2,),VLOOKUP($B9,$A$1:$H$4,5,FALSE)*ROUNDUP(MAX($C9-$D$3)/F2,))
Hi Ali, thanks for the fast reply!
=SUM(VLOOKUP($B9,$A$1:$H$4,2,FALSE),VLOOKUP($B9,$A$1:$H$4,3,FALSE)*ROUNDUP((MIN($D$3,$C9)-1)/$D$2,),VLOOKUP($B9,$A$1:$H$4,5,FALSE)*ROUNDUP(MAX($C9-$D$3)/F2,))
It is similar to how I split up my INDEX MATCH formula, it a sum of:
- VLOOKUP($B9,$A$1:$H$4,2,FALSE) : price of first 1km
- VLOOKUP($B9,$A$1:$H$4,3,FALSE)*ROUNDUP((MIN($D$3,$C9)-1)/$D$2,) : VLOOKUP($B9,$A$1:$H$4,3,FALSE) is the price of every 400m for each type of taxis, ROUNDUP((MIN($D$3,$C9)-1)/$D$2,) is to find out how many 400m there is in the distance
- VLOOKUP($B9,$A$1:$H$4,5,FALSE)*ROUNDUP(MAX($C9-$D$3)/F2,) : ^ similar to above but for the 350m
May I know if there are any suggestions with regards to my INDEX MATCH formula?
Last edited by onewishtobegranted; 08-28-2020 at 09:22 AM.
No idea what you are up to, but this is that formula using INDEDX/MATCH
=SUM(INDEX($B$1:$B$4,MATCH($B9,$A$1:$A$4,0)),INDEX($C$1:$C$4,MATCH($B9,$A$1:$A$4,0))*ROUNDUP((MIN($D$3,$C9)-1)/$D$2,),INDEX($E$1:$E$4,MATCH($B9,$A$1:$A$4,0))*ROUNDUP(MAX($C9-$D$3)/F2,))
Hi Bob,
Thank you. I see that my INDEX MATCH formulas are right. Is it a need to add the headers into the array? aka A1:A4
You are matching against those, so only in the MATCH function.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks