Nested if()

Ptipok

Hello great community.

I'm new in Smartheet and I've a problem and even don't know if a solution exists. I'm trying to create a sort of automatized timesheet for my team.

I would like them to connect to the same sheet. They have to select their name in a list ("Team" column), and the type of mission they've managed in an other list ("Mission" column). But each one doesn't cost the same per day, and the cost is even different depending on the mission.

So, I would like that the cost / day changes automatically ("Cost/day" column) depending on what is selected on "Team" column and "Mission" column.

I hope I'm clear... I tried with nested if() but it doesn't work. If you have a solution it would be really great for me.

Please find below the screenshot and my formula. Thanks a lot for your help. Have a nice day.

 

=IF(Team1 = "Charlotte"; IF(Mission1 = "Conception"; 400; IF(Mission1 = "Production"; 300; IF(Team1 = "Géraldine"; IF(Mission1 = "Conception"; 1000; IF(Mission1 = "Production"; 800))))))

 

 

 

Nic Larsen

This should give you a start on how to build your nested formula, since I don't know how many combinations or conditions you have. 

=IF(AND(Team@row = "Charlotte"; Mission@row = "Production"); 800; IF(AND(Team@row = "Charlotte"; Mission@row = "Conception"); 400; IF(AND(Team@row = "Geraldine"; Mission@row = "Production"); 800; IF(AND(Team@row = "Geraldine"; Mission@row = "Conception"); 1000))))

You may be able to shorten this with some OR statements if some combo's are the same. 

Ptipok

Hello,

yes it's perfect. Thank you so much for your help and your quick answer

Have a nice day

Ptipok

Dear Nic,

I tried to follow your advice to shorter my fomula, using OR but finally, it doesn't work. For sure, I don't use the right method...

Indeed I have certain mission that cost 0. So I tried this :

 

=IF(AND(Team@row = "Charlotte"; Mission@row = "Conception"); 800; IF(AND(Team@row = "Charlotte"; Mission@row = "Production"); 500; IF(AND(Team@row = "Charlotte"; Mission@row = "Gestion de projet"); 800; IF(AND(Team@row = "Charlotte"; Mission@row = "Déploiement"); 400; IF(AND(Mission@row = "Mission interne", IF(OR(Mission@row = "Consulting interne", Mission@row = "Avant-vente"); 0;))))))))))

 

Thanks a lot to help me again. Have a nice day

Nic Larsen

So find it helps at times to split out each of your IF statements to make sure they all line up and then re-stack them.

When I did that on your formula, I found an incomplete IF formula. I also moved your OR statement to the front as the initial check. 

= IF(OR(Mission@row = "Consulting interne", Mission@row = "Avant-vente"); 0; IF(AND(Team@row = "Charlotte"; Mission@row = "Conception"); 800; IF(AND(Team@row = "Charlotte"; Mission@row = "Production"); 500; IF(AND(Team@row = "Charlotte"; Mission@row = "Gestion de projet"); 800; IF(AND(Team@row = "Charlotte"; Mission@row = "Déploiement"); 400;

IF(AND(Mission@row = "Mission interne", ???

I think you need to complete that piece, and add it to the end after 400; and then give it another try. Unless I'm not understanding your initial OR statement. 

 

Ptipok

Hello Nic,

It works. Thanks a lot again.

Have a nice day.