Hi, I really need help or guidance with the condition below. I have an existing excel report that I would like to transition it into Smartsheet but this formula is really driving me up the wall and I just can’t figured out what I’m doing wrong! Works well in excel but not in Smartsheet. I’ve also tried to break it down by each statement and it only works on the first condition and the rest I would get a result of #Incorrect Argument or #Unparseable.
Here's what I’m trying to do.
If the Incentive Group = % Net New and TOTAL NET NEW FY END 2021 is less than Silver then “None”,
If the Incentive Group = % Net New and TOTAL NET NEW FY END 2021 is greater than or equal Silver and less than Gold then “Silver”,
If the Incentive Group = % Net New and TOTAL NET NEW FY END 2021 is greater than or equal Gold and less than Platinum then “Gold”,
If the Incentive Group = % Net New and TOTAL NET NEW FY END 2021 is greater than Platinum then “Platinum”,
Otherwise, if Incentive Group = Per Store Growth then VLookup National Incentive with lookup between Per Store $1: NAFA$5
Here’s the formula in Smartsheet:
=IF(AND([Incentive Group]@row = "% Net New", [Total Net New FY End 2021]@row < ROUND(Silver@row, 0)), "None", IF(AND([Incentive Group]@row = "% Net New", [Total Net New FY End 2021]@row >= ROUND(Silver@row, [Total Net New FY End 2021]@row < ROUND(Gold@row, 0)), "Silver", IF(AND([Incentive Group]@row = "% Net New", [Total Net New FY End 2021]@row >= ROUND(Gold@row, [Total Net New FY End 2021]@row < ROUND(Platinum@row, 0)), "Gold", IF(AND([Incentive Group]@row = "% Net New", [Total Net New FY End 2021]@row > ROUND(Platinum@row, 0)), "Platinum", IF([Incentive Group]@row = "Per Store Growth", VLOOKUP([National Incentive Count]@row, $[Per Store]$1:$NAFA$5, 3, 0))))))))
Any guidance and assistance is much appreciated and thanks in advance!
