June 25, 2016

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

Power BI

Power Query

April 21, 2015

June 25, 2016

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

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

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!

June 25, 2016

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

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

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

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:

Michael Bruce

R vd Bosch

Shawn Geiger

Naser Aljed

Komal Limbu

Traicey Dwyer

Jan-Eric Borén

Rao Sharafat Ali

Abhishek Nar

ruben pillay

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