# Using nested IF test with AND tests stacked for multiple criteria in one column

Topic Labels: Formulas
Solved
211 2
cancel
Showing results for
Did you mean:
4 - Data Explorer

Hello!

I am trying to create a formula that categorises ships by type, then size category, and if they meet both of those criteria they are assigned a value that is annual fuel consumption appropriate for the vessel's size category. If not, the IF test continues through all size categories and finally ends on "NA" if nothing it true.

I would have thought I would be able to nest like the below example, but for some reason it only generates the correct answer for the first two size categories, then everything higher in size gets the same second highest fuel value:

IF(

AND(

{VESSEL TYPE} = "SHIP TYPE1" ,

{SIZE CATEGORY}<=1000),

"XX Fuel consumption value",

IF(

AND(

{VESSEL TYPE} = "SHIP TYPE1" ,

{SIZE CATEGORY}>=1000<=1999),

"XX Fuel consumption value",

IF(

AND(

{VESSEL TYPE} = "SHIP TYPE1" ,

{SIZE CATEGORY}>=2000<=2999),

"XX Fuel consumption value",

"NA")))

Below is the actual formula if that helps anyone understand. I also hoped to be able to have all vessel types and size category fuel info on one column but started simple with just one column for each ship type for now:

IF(
AND(
{Vessel type}="Bulk",
"1505.839363",
IF(
AND(
{Vessel type}="Bulk",
"3528.268375",
IF(
AND(
{Vessel type}="Bulk",
"4828.456419",
IF(
AND(
{Vessel type}="Bulk",
"6781.354246",
IF(
AND(
{Vessel type}="Bulk",
"9777.214307",
IF(
AND(
{Vessel type}="Bulk",
"12310.17463",
"NA"))))))
1 Solution

Accepted Solutions
6 - Interface Innovator

Yeah, it seems like Airtable doesn't like the math as you have it. Try using a nested AND().

IF(
AND(
{Vessel type}="Bulk",
"1505.839363",
IF(
AND(
"3528.268375",
IF(
AND(
"4828.456419",
IF(
AND(
"6781.354246",
IF(
AND(
{Vessel type}="Bulk",
"9777.214307",
IF(
AND(
{Vessel type}="Bulk",
"12310.17463",
"NA"))))))
2 Replies 2
6 - Interface Innovator

Yeah, it seems like Airtable doesn't like the math as you have it. Try using a nested AND().

IF(
AND(
{Vessel type}="Bulk",
"1505.839363",
IF(
AND(
"3528.268375",
IF(
AND(
"4828.456419",
IF(
AND(
"6781.354246",
IF(
AND(
{Vessel type}="Bulk",