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 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
-
Mark - You are the best!!! Thanks so much! It worked perfectly :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!