Help

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

Topic Labels: Formulas
Solved
Jump to Solution
533 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Abigail0102
5 - Automation Enthusiast
5 - Automation Enthusiast

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",
{Dead weight tonnage (DWT)}<=9999),
"1505.839363",
IF(
AND(
{Vessel type}="Bulk",
{Dead weight tonnage (DWT)}>=10000<=34999),
"3528.268375",
IF(
AND(
{Vessel type}="Bulk",
{Dead weight tonnage (DWT)}>=35000<=59999),
"4828.456419",
IF(
AND(
{Vessel type}="Bulk",
{Dead weight tonnage (DWT)}>=60000<=99999),
"6781.354246",
IF(
AND(
{Vessel type}="Bulk",
{Dead weight tonnage (DWT)}>=100000<=199999),
"9777.214307",
IF(
AND(
{Vessel type}="Bulk",
{Dead weight tonnage (DWT)}>=200000),
"12310.17463",
"NA"))))))
1 Solution

Accepted Solutions
Hamlin_Krewson2
6 - Interface Innovator
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",
{Dead weight tonnage (DWT)}<=9999),
"1505.839363",
IF(
AND(
{Vessel type}="Bulk",AND({Dead weight tonnage (DWT)}>9999,{Dead weight tonnage (DWT)}<35000)),
"3528.268375",
IF(
AND(
{Vessel type}="Bulk",AND({Dead weight tonnage (DWT)}>34999,{Dead weight tonnage (DWT)}<60000)),
"4828.456419",
IF(
AND(
{Vessel type}="Bulk",AND({Dead weight tonnage (DWT)}>59999,{Dead weight tonnage (DWT)}<100000)),
"6781.354246",
IF(
AND(
{Vessel type}="Bulk",
AND({Dead weight tonnage (DWT)}>99999,{Dead weight tonnage (DWT)}<200000)),
"9777.214307",
IF(
AND(
{Vessel type}="Bulk",
{Dead weight tonnage (DWT)}>=200000),
"12310.17463",
"NA"))))))

See Solution in Thread

2 Replies 2
Hamlin_Krewson2
6 - Interface Innovator
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",
{Dead weight tonnage (DWT)}<=9999),
"1505.839363",
IF(
AND(
{Vessel type}="Bulk",AND({Dead weight tonnage (DWT)}>9999,{Dead weight tonnage (DWT)}<35000)),
"3528.268375",
IF(
AND(
{Vessel type}="Bulk",AND({Dead weight tonnage (DWT)}>34999,{Dead weight tonnage (DWT)}<60000)),
"4828.456419",
IF(
AND(
{Vessel type}="Bulk",AND({Dead weight tonnage (DWT)}>59999,{Dead weight tonnage (DWT)}<100000)),
"6781.354246",
IF(
AND(
{Vessel type}="Bulk",
AND({Dead weight tonnage (DWT)}>99999,{Dead weight tonnage (DWT)}<200000)),
"9777.214307",
IF(
AND(
{Vessel type}="Bulk",
{Dead weight tonnage (DWT)}>=200000),
"12310.17463",
"NA"))))))

Amazing! Worked like magic, thank you I was going cross eyed trying to work it out 🙂