Register
Log In
Unanswered
Home
June 25, 2016
Offline
Another formula need help:
Let said there are few type of meals
- breakfast (max for 6 person) 30 dollar per person
- lunch ( max for 7 person ) 40 dollar per person
- dinner ( max for 4 person ) 50 dollar person
How to make formula for choose any type and number of person will be cap on different type of meal criteria?
Example:
Dinner 6 person but only can claim for 4x50dollar = 200 dollar
Thanks for the help!
Dashboards
June 25, 2016
Offline
Hi again rathanak
Try this:
=INDEX({30,40,50},MATCH(A2,{"Breakfast","Lunch","Dinner"},0))*(MIN(INDEX({6,7,4},MATCH(A2,{"Breakfast","Lunch","Dinner"},0)),B2))
Change the ranges accordingly.
A2 = Meal Type
B2 = No Of Persons
Sunny Kow
Dashboards
Power BI
Power Query
April 21, 2015
Offline
Hi Sunny. Nice solution! But what would it help when you try to explain some things about this formula. Why this solution? How do you build this up and why in this way? That'll help more people I'm sure!
Thanks!
Frans
June 25, 2016
Offline
SunnyKow said
Hi again rathanakTry this:
=INDEX({30,40,50},MATCH(A2,{"Breakfast","Lunch","Dinner"},0))*(MIN(INDEX({6,7,4},MATCH(A2,{"Breakfast","Lunch","Dinner"},0)),B2))
Change the ranges accordingly.
A2 = Meal Type
B2 = No Of Persons
Sunny Kow
Your formula nice,I never thought of.I try nd let you know
Dashboards
June 25, 2016
Offline
Hi Frans
It is not easy to explain the workings but I will try my best. I have broken down the formula for easy reference.
Note : A2 = Meal Type, B2 = No Of Persons
STEP 1 : Determine the claim amount allowable per person for each type of meal
First I determine what was chosen in cell A2 using the MATCH function. It will return a number e.g. 1 = Breakfast, 2=Lunch , 3= Dinner. With this number, I then use the INDEX function to extract the claim allowance i.e.1=30, 2=40, 3=50.
INDEX({30,40,50},MATCH(A2,{"Breakfast","Lunch","Dinner"},0))
STEP 2 : Determine maximum person allowed to claim for each type of meal
Using the same method in STEP 1, I determine what is the maximum number of person allowable. Breakfast=6 person, Lunch=7 person, Dinner=4 person
INDEX({6,7,4},MATCH(A2,{"Breakfast","Lunch","Dinner"},0))
STEP 3 : Determine whether the number of actual person claimed is more than the maximum allowable (STEP 2)
I use the MIN() to get the lower of the two values (between value entered into cell B2 and the value determined in STEP 2 above.
MIN(INDEX({6,7,4},MATCH(A2,{"Breakfast","Lunch","Dinner"},0)),B2)
STEP 4 : Multiply the claim amount (STEP 1) against the number of person allowable (STEP 3)
This will give me the answer
=INDEX({30,40,50},MATCH(A2,{"Breakfast","Lunch","Dinner"},0))*(MIN(INDEX({6,7,4},MATCH(A2,{"Breakfast","Lunch","Dinner"},0)),B2))
When I initially did the formulas above, they were created separately in helper columns. This will allow me to test and ensure that the figures extracted are correct. Finally I combined them together to get the final formula.
I hope this helps to explain the formula given.
Sunny Kow
Dashboards
Power BI
Power Query
April 21, 2015
Offline
SunnyKow wrote: "I hope this helps to explain the formula given."
This sure did help Sunny! Thanks a lot sharing this with me/us.
I didn't know that it was possible to give the three type of eating, the three values etc. separately. Thought you have to give a cell reference for a serie of cells there. And now I understand the using of the MIN function which suddenly 'popped up'.
Half of Excel solutions have to do with understanding what is asked for and knowing which formula can help. And then of course the proper use of the formula or combination of formulas. What I already like about this Forum is the way you say different approaches can lead to the same solution. And one person is for instance better or more familiar with the index and match function and another likes a VBA solution or so.
This really helps others (as me) to climb up to a higher Excel level.
I hope you keep this in mind when you help somebody with his/her question and try to give some explanation again about your solution.
Thanks very much again!
Dashboards
June 25, 2016
Offline
Hi Frans
It was way pass midnight over here when I posted that long explanation.
I have attached the working for better clarification.
Hope this helps.
Sunny Kow
June 25, 2016
Offline
SunnyKow said
Hi FransIt is not easy to explain the workings but I will try my best. I have broken down the formula for easy reference.
Note : A2 = Meal Type, B2 = No Of Persons
STEP 1 : Determine the claim amount allowable per person for each type of meal
First I determine what was chosen in cell A2 using the MATCH function. It will return a number e.g. 1 = Breakfast, 2=Lunch , 3= Dinner. With this number, I then use the INDEX function to extract the claim allowance i.e.1=30, 2=40, 3=50.
INDEX({30,40,50},MATCH(A2,{"Breakfast","Lunch","Dinner"},0))
STEP 2 : Determine maximum person allowed to claim for each type of meal
Using the same method in STEP 1, I determine what is the maximum number of person allowable. Breakfast=6 person, Lunch=7 person, Dinner=4 person
INDEX({6,7,4},MATCH(A2,{"Breakfast","Lunch","Dinner"},0))
STEP 3 : Determine whether the number of actual person claimed is more than the maximum allowable (STEP 2)
I use the MIN() to get the lower of the two values (between value entered into cell B2 and the value determined in STEP 2 above.
MIN(INDEX({6,7,4},MATCH(A2,{"Breakfast","Lunch","Dinner"},0)),B2)
STEP 4 : Multiply the claim amount (STEP 1) against the number of person allowable (STEP 3)
This will give me the answer
=INDEX({30,40,50},MATCH(A2,{"Breakfast","Lunch","Dinner"},0))*(MIN(INDEX({6,7,4},MATCH(A2,{"Breakfast","Lunch","Dinner"},0)),B2))
When I initially did the formulas above, they were created separately in helper columns. This will allow me to test and ensure that the figures extracted are correct. Finally I combined them together to get the final formula.
I hope this helps to explain the formula given.
Sunny Kow
Nice explanation,you r awesome
I use IFs it works too,really thanks your efforts,wish you good luck
Dashboards
June 25, 2016
Offline
Hi Rathanak
Glad to know it helps.
There are always more than one way to solve a problem. It doesn't matter if it is a long/short/fantastic/fast calculating formula or whatever.
It must give the result that the user wants. That is the most important thing for the user.
Even if a VBA procedure takes 5 minutes to give the result, it is deemed very good by my colleagues. Without the procedure they may take an hour or more to get the same result. I am not an Excel expert but I will try to help out if I can.
Sunny Kow
Dashboards
Power BI
Power Query
April 21, 2015
Offline
Hi Sunny, thanks for your extra effort providing us with the Excel-sheet.
I see how you worked up towards the complete formula.
As said: the working with the variables in this way and also the way you use the MIN function were for me the new parts in this case.
I also like to help other people, but as far as I see the questions here it's difficult to help them with the knowledge I have...... 🙂
Keep up with the good work!
June 25, 2016
Offline
Rea
SunnyKow said
Hi RathanakGlad to know it helps.
There are always more than one way to solve a problem. It doesn't matter if it is a long/short/fantastic/fast calculating formula or whatever.
It must give the result that the user wants. That is the most important thing for the user.
Even if a VBA procedure takes 5 minutes to give the result, it is deemed very good by my colleagues. Without the procedure they may take an hour or more to get the same result. I am not an Excel expert but I will try to help out if I can.
Sunny Kow
Really appreciated nd thank u for your efforts,wish u good luck nd success what u wish
Most Users Ever Online: 57
Currently Online:
1 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Susan Booze
Rita Hrez
David Innes
preeti bhulabhai
Charles Klasek
Patrick Isedu
Emma Hayward
Vamseedhar G
NA NA
Max Neumann
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
