IF, AND, OR nested

[Marc_Chenut]

Hi,

I have a price table with 12 rows per activity that refers to the number of people.

I have 4 different column

1. “With transport / without transport”
2. “Transport price for 2 to 4 people”
3. “Transport price for 5 to 6 people”
4. “Transport price for 7 to 12 people”.

In “Transport price”, I am looking to get the right transport price depending IF it’s with transport. Then depending of the number of people, I want to grab the right column/price.

Could you help please :slightly_smiling_face: I really can’t find the right formula.

Many thanks

[Ela_Automatela_]

Hi! Can you please share the table you’re using? I am not sure if I know from the description how your table looks like.

[Marc_Chenut]

Hi Ela,
Thanks for your help.

Here is how it is built :

The end results in column “Transport price” :
From 1 to 4 participants, it should grab data from “transport 2 à 4 people”
From 5 to 6 participants, it should grab data from “transport 5 à 6 people”
From 7 to 12 participants, it should grab data from “transport 7 à 12 people”

[Marc_Chenut]

Or in another table that looks like this :

Last Column “Transport price” should grab data from one of the 3 columns “Prix pour 2”, or "prix pour 6, or “prix pour 12”
depending if
“N” column data is between 2 to 4, or 5 to 6, or 7 to 12.

Sorry, I try to be as clear as possible, or as specific as possible and it’s not that easy :slightly_smiling_face: Hope you will be able to get what I am looking.

[Ela_Automatela_]

Ok, so if I understood correctly you need below formula:

IF(AND({Number of people} >= 2, {Number of people} <= 4), {Price 1},
	IF(AND({Number of people} >= 5, {Number of people} <= 6), {Price 2}, 
		IF(AND({Number of people} >=7, {Number of people} <= 12), {Price 3}, "Error")
	)
)

You need to replace Price 1, Price 2 and Price 3 with your column names (the ones with prices for transport). “Number of people” in my case is what you have in “Participants”, so you have to rename that too. Just be careful to not miss any parenthesis or something. Let me now if it worked.

Ah, and if the number of people is below 2 and above 12 it will return “Error”.

[Marc_Chenut]

It works ! Thanks a lot Ela ! It really helps.