Formulas  IF This then do this calculation
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
Answers

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 multiyear)]@row < 100000, [Contract ASV (highest year ASV if multiyear)]1 * 0.2, 0)
Rev Share 10% Column:
=IF([Contract ASV (highest year ASV if multiyear)]@row >= 100000, [Contract ASV (highest year ASV if multiyear)]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

Mark  You are the best!!! Thanks so much! It worked perfectly :)
Help Article Resources
Categories
Check out the Formula Handbook template!