Formulas - IF This then do this calculation

Anna W.

Hoping someone can help! I want to do a formula IF Contract ASV is less than 100000, then multiply Contract ASV by .20 and put that amount in the Rev Share 20% of per year ASV in USD field, but IF Contract ASV is greater than 100000, then multiply Contract ASV by .1 and put in the Rev Share 10% of per year ASV in USD column.

Seems like I would need separate formulas in each of the columns, one for the 20% column and one for the 10% column, and then for the one that it is not applicable to the number showing would be zero for that column and the returned value would be in the other column.

I have looked through quite a few articles, but have not found anything where you are doing a calculation.

Thanks!

Anna

Mark Safran

Hi Anna,

You're correct about there needing to be a separate formula for each column. The value_if_true of the IF( ) function is where you'll actually do the math. Try these out:

Rev Share 20% Column:

=IF([Contract ASV (highest year ASV if multi-year)]@row < 100000, [Contract ASV (highest year ASV if multi-year)]1 * 0.2, 0)

Rev Share 10% Column:

=IF([Contract ASV (highest year ASV if multi-year)]@row >= 100000, [Contract ASV (highest year ASV if multi-year)]1 * 0.1, 0)

(I assumed if the value was exactly 100,000 it would go in the 10% column (hence the > = in this formula).

-MS

Anna W.

Mark - You are the best!!! Thanks so much! It worked perfectly :)