IF AND Function
Dear Smartsheet Wizard,
I'm trying to create a function but I'm running into an error.
The IF  And function I'm creating basically needs to do the following:
If salesperson 1 is = "Yan" then deduct 100 from Profit and his commission should be profit*0.5. Otherwise, deduct 100 from Profit and commission should be profit*0.4.
The second IF is:
IF Date>9/3/19 and Dealer = "B C" than if salesperson 1 is "Yan" then deduct 175 from Profit and his commission should be profit*0.5. Otherwise, deduct 175 from Profit and commission should be profit*0.4.
What I did looks something like this.
=IF(AND(Date1513>9/3/19,Dealership1="B C",[Sales Person 1]1513="Yan",)[Net Profit]1513  175) * 0.5),([Net Profit]1513  175) * 0.4)IF([Sales Person 1]1513= "Yan", ([Net Profit]1513  100) * 0.5), ([Net Profit]1513  100) * 0.4))
Please help.
Thank you.
Comments

Yeshaya,
What is the significance of the date (9/3/19)? Is there a reason that is in your formula or did you mean to reference a cell with a date inside of it?
If you put this formula in other cells then it will always reference 9/3/19, is that what you want?
Connor
Connor Hartford

I spot 2 errors off the top just looking at the formula. both are bolded below
=IF(AND(Date1513> 1. 9/3/19 ,Dealership1="B C",[Sales Person 1]1513="Yan",)[Net Profit]1513  175) * 0.5),([Net Profit]1513  175) * 0.4 2. )I F([Sales Person 1]1513= "Yan", ([Net Profit]1513  100) * 0.5), ([Net Profit]1513  100) * 0.4))
1. Date needs to be told it is a date. to do this you use the date formula
>date(2019, 9,3)
2. you have already fulfilled all potential conditions of your if statement. You cannot simply add on another if. a simpler example of what you have done is below
=if( a = 1, true, false)if(a = 2,1,0)
You see how the second if statement has not clear relation to the first?
Each if statement gets 2 returns, a true and a false. When you create a branch you add more of what I like to call filter levels for the data, until you get to what you want.
Your formula and description don't really give enough information for us to give you a full answer, as we don't know what particular condition causes the change between the two if statements. Is it because the date is different? or the dealership is different? the only thing I see really correlating the two formulas is "Yan".

Hi L@123
Thank you for your time and thoughts.
There are two conditions that cause a change; namely,
1. If the salesperson is Yan.
2. If the dealer is B C.
If the salesperson is Yan then he gets 50% of Net Profit. Otherwise, the salesperson gets 40%.
If the date is after 9/3/19 and the dealer is B C, deduct $175 from the profit. Otherwise, deduct $100.
I very much appreciate your advice.

Now I'm trying to do just the below formula for starters. For some reason, it still shows an error. Please help.
IF(AND(Dealership1534 = "B C", >DATE(2019, 9, 2), [Sales Person 1]1534 = "Yan"), ([Net Profit]1534  175) * 0.5, ([Net Profit]1534  175) * 0.4)
Thank you!

you just post > Date() What is greater than the specified date? an example would be
Date1534 > DATE(2019,9,2)
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 454 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives