Hello Experts!
My current IF statement, takes the number of employees and depending on the range, produces a per-employee price from the pricing matrix (on another sheet).
QUESTION:
Calculate per employee as demonstrated above, but if the per employee cost equals less than $3,500, the cost will be a minimum of $3,500. How do I incorporate that into the existing formula?
Here's the pricing matrix screen shot:
Thank you for your help!
Here's a closer view of my existing formula:
=IF('Assurance Offerings'!$B$7>150,'Pricing Matrix v1.0'!$R$22,IF('Assurance Offerings'!$B$7>100,'Pricing Matrix v1.0'!$Q$22,IF('Assurance Offerings'!$B$7>75,'Pricing Matrix v1.0'!$P$22,IF('Assurance Offerings'!$B$7>50,'Pricing Matrix v1.0'!$O$22,IF('Assurance Offerings'!$B$7>25,'Pricing Matrix v1.0'!$N$22,IF('Assurance Offerings'!$B$7>14,'Pricing Matrix v1.0'!$M$22,IF('Assurance Offerings'!$B$7>0,'Pricing Matrix v1.0'!$L$22)))))))
My current IF statement, takes the number of employees and depending on the range, produces a per-employee price from the pricing matrix (on another sheet).
QUESTION:
Calculate per employee as demonstrated above, but if the per employee cost equals less than $3,500, the cost will be a minimum of $3,500. How do I incorporate that into the existing formula?
Here's the pricing matrix screen shot:
Thank you for your help!
Here's a closer view of my existing formula:
=IF('Assurance Offerings'!$B$7>150,'Pricing Matrix v1.0'!$R$22,IF('Assurance Offerings'!$B$7>100,'Pricing Matrix v1.0'!$Q$22,IF('Assurance Offerings'!$B$7>75,'Pricing Matrix v1.0'!$P$22,IF('Assurance Offerings'!$B$7>50,'Pricing Matrix v1.0'!$O$22,IF('Assurance Offerings'!$B$7>25,'Pricing Matrix v1.0'!$N$22,IF('Assurance Offerings'!$B$7>14,'Pricing Matrix v1.0'!$M$22,IF('Assurance Offerings'!$B$7>0,'Pricing Matrix v1.0'!$L$22)))))))