If AND OR Help

New Contributor

Hi, I am trying to merge a couple of formulas in my spreadsheet.

This is what I want

=if K2 = "Mainstream", IF(J2<18.5,(L2*0.75),(13.5*I2))

=if K2 = "Specialist/PRU" , IF(J2<47,(L2*0.75),(35.25*I2))

Mainstream and Specialist/PRU are the only options for cell K2 but they each come with their own costing calculations. 

Can anybody please provide the formula for all of this to appear in one cell?

 

Many thanks

2 Replies

@Adele1981 Try this:

=IF(K2="Mainstream",IF(J2<18.5,(L2*0.75),(13.5*I2)),IF(J2<47,(L2*0.75),(35.25*I2)))

 

 

Thank you @Riny_van_Eekelen
No it wouldn't calculate correctly that way,
I managed to resolve it using
=IFS(K6="Mainstream", IF(J6<=18,L6*0.75,13.5*I6), OR(K6="Specialist/PRU"),IF(J6<=47,L6*0.75,35.25*I6))