How to use If functions for 3 criteria

[Eric Tsang]

Dear all

I would like to know if i have the following criteria

a) Adult price at $10
b) Child perice at $15
c) Buyout price at $100
d) per unit , total costs of $100, and divided by the number of pax



a) - c) when select the cost will be as stated. If client select d) Per unit, the cost will be divided by number of persons

[AliGW]

Three of us took the time to offer help in your last thread. Please do us the courtesy of a reply if you are expecting to receive further help here. We are all unpaid volunteers.

[Eric Tsang]

Dear Ali

I hope i did what you told me..

My apology for not fulfilling the rules

Eric

[AliGW]

No - I was not asking for rep points. I was asking for an acknowledgement of the help offered in your last two threads, actually. You have just left them without going back and saying whether or not the solution worked. It’s not a rule, it’s just common courtesy.

[Eric Tsang]

Aligw

I have thanks all people who answered my thread.

[AliGW]

Thank you. Please don't take those who help here for granted in future.

I will have a look at your problem now.

[AliGW]

Try this:

=ROUND(VLOOKUP(D12&"*",$A$11:$B$14,2,0)/IF(D12="Buyout",$E$11,1),0)

[Eric Tsang]

dear Aligw

It works thanks

But i do not understand


I was trying myself by using If + and ... or even choose + If.

Because, there may be some more will not be required to divide by the no of pax,i.e E11

I thought we would have a range to list out all the possible "word"that do not need to divide the no of pax


Is there any?!

[AliGW]

I don't have a clue what you are asking me, sorry. However, you can add items to the lookup list. Using nested IF statements is rarely, if ever, the most efficient solution.

Maybe you want this:

=ROUND(VLOOKUP(D12&"*",$A$11:$B$14,2,0)/IF(OR(D12="Adult",D12="Child"),1,$E$11),0)

[Eric Tsang]

Dear Aligw

Thanks a lot

[AliGW]

You're welcome.

[Eric Tsang]

Dear Aligw

I have found Ifs, as i am using 2016

=IFS(D15="adult",B14,D15="Buyout",B14,D15="Child",B14,TRUE,B14/$E$11)

But i am still looking for solution that can be use for 2010 ( as my colleauge are using this version)

I also saw one of the method, which they can refer to a range of the criteria .. something a combination of "choose"and "if"
as an alternative for Nest If

Eric

[AliGW]

I have given a solution that works in Excel 2010. Why don't you use it? It's ridiculous trying to do this with nested IF statements - it will be a nightmare going forward - my solution is easily scalable by extending the lookup range.

[Eric Tsang]

Dear Aligw

As i may have a list of "criteria "

I do not understand your formula... i am very new in Excel

Eric

[AliGW]

Which is precisely why you should NOT be using nested IFs! If you stubbornly insist on using them and reject my solution, then you will never learn what Excel can do and, frankly, there's no point in asking us to advise you.

Have a look at the attached. Add some more items and prices to the price list on the left, then add one of the new items to cell D18 and see what happens.

If you need the formula explaining, then say so.

[Eric Tsang]

Dear Aligw

I found that your formula can keep adding "items"
=IF(D12="","",ROUND(VLOOKUP(D12&"*",$A$11:$B$50,2,0)/IF(OR(D12="Adult",D12="Child",D12="Buyout",D12="Another"),1,$E$11),0))

It works

I think the problem solved

eric

[AliGW]

That should NOT work based on what you said about Buyout in the first place.

You would be far better off understanding the formula before trying to update it.

The OR clause should include ONLY the items that are priced as is. Anything that is divided by the number of people (pax as you call it) should NOT be in the OR statement.

I doubt the problem is solved - but if you are happy to walk away without understanding the solution, then that's fine. However, for completeness (and for anyone else reading this), here's a breakdown:

=IF(D12="","",

If D12 is blank, return nothing (blank).

VLOOKUP(D12&"*",$A$11:$B$50,2,0)

Lookup D12 in the range A11:A50 and return what is in the cell to the right of it (in B11:B50).

IF(OR(D12="Adult",D12="Child")

If D12 is EITHER Adult OR Child ...

/... 1, ...)

divide it by 1 (in other words leave it as it is).

/ ... $E$11)

Anything that isn't one of these two is divided by the value in E11 (3).

ROUND(... ,0)

The result is rounded to a whole number.