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"))))))
Best answer by Hamlin_Krewson2
Yeah, it seems like Airtable doesn't like the math as you have it. Try using a nested AND().
