IF AND Function

Yeshaya · 09/06/19

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.

Connor Hartford · 09/06/19

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

L_123 · 09/06/19

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".

Yeshaya · 09/09/19

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.

Yeshaya · 09/17/19

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!

L_123 · 09/17/19

you just post > Date() What is greater than the specified date? an example would be

Date1534 > DATE(2019,9,2)

IF AND Function

Comments

Categories