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

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

SunnyKow said
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  

Your formula nice,I never thought of.I try nd let you know

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

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!

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

SunnyKow said
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  

Nice explanation,you r awesome

I use IFs it works too,really thanks your efforts,wish you good luck

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

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!

Rea

SunnyKow said
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  

Really appreciated nd thank u for your efforts,wish u good luck nd success what u wish