# IF, AND, OR nested

Topic Labels: Formulas
914 5
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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

5 Replies 5
5 - Automation Enthusiast

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.

5 - Automation Enthusiast

Hi Ela,

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”

5 - Automation Enthusiast

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.

5 - Automation Enthusiast

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”.

5 - Automation Enthusiast

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