Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Formula Writing Help Needed - IFS

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

I’m currently migrating from using google sheets to Airtable, I have a majority of my formulas working, however, I need assistance with this:

=IFS(A2>1000001,A254%,A2>=400001,A251%,A2>=200001,A247%,A2>=100001,A242%,A2>=80001,A237%,A2>=60001,A234%,A2>=40001,A231%,A2>=20001,A227%,A2>=10001,A224%,A2>=1,A221%)

The formula looks up a value and depending on the amount it then calculates a value based upon a percentage.

I look forward to any assistance I can get

2 Replies 2
18 - Pluto

It looks like you are mixing the syntax of an `IF` with a `SWITCH`.
When dealing with ranges of values, you need to nest multiple `IF` statements. Here is an example. The numbers don’t exactly match yours, but you should get the general pattern.

``````IF(A2 > 1000,
A2 * 0.54,
IF(A2 >= 400,
A2 * 0.51,
IF(A2 >= 200,
A2 * 0.47
)))
``````
4 - Data Explorer

Thanks for the feedback and guess what? It works, this is the formula in effect:

IF({Deal : Automated Value} > 1000001,
{Deal : Automated Value} * 0.54,
IF({Deal : Automated Value} > 400001,
{Deal : Automated Value} * 0.51,
IF({Deal : Automated Value} > 200001,
{Deal : Automated Value} * 0.47,
IF({Deal : Automated Value} > 100001,
{Deal : Automated Value} * 0.42,
IF({Deal : Automated Value} > 80001,
{Deal : Automated Value} * 0.37,
IF({Deal : Automated Value} > 60001,
{Deal : Automated Value} * 0.34,
IF({Deal : Automated Value} > 40001,
{Deal : Automated Value} * 0.31,
IF({Deal : Automated Value} > 20001,
{Deal : Automated Value} * 0.27,
IF({Deal : Automated Value} > 10001,
{Deal : Automated Value} * 0.24,
IF({Deal : Automated Value} > 1,
{Deal : Automated Value} * 0.21
))))))))))