Include an If Statement when one is already in use

[lcfierro]

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)))))))

 

[wsjackman]

try
=max(3500,your equation,)

 

[lcfierro]

I need the minimum to be $3,500, does the MIN= function work for that purpose? I also have left out one thing in my formula, I apologize for that.

Formula is:

=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)))))))*'Assurance Offerings'!$B$7

Again, the result of that calculation, needs to be a minimum of $3,500. How do I make sure the result doesn't come under that amount?

I would think that it would be yet another IF statement. For example, if the result is <3500 the result will be 3500.

 

[wsjackman]

the max function
=max(3500, function())
with 3500 as one of the options will not give an answer less than 3500
it looks at your function and compares it to 3500 and reports the one with the highest value.
I know it is not intuative to use a max() function to establish a minimum limit output. you also use a min() function to establish a maximum limit output.

the reason I did not copy your equation into the max equation is that I am notorious for typos.

 

[lcfierro]

i'll look out for the typos, would rather see your suggestion with my existing formula. would you mind?

 

[lcfierro]

Disregard previous screen shots. Here are true and updated photos:

I'd like to focus on cell W17

As you can see, in cell B7, there are 15 employees. This refers back to the pricing matrix here, in cell M22:

It is displaying a total of $1500 for the total initial investment. (# of employees * price per employee). However, I don't want this initial investment cost to be less than $3,500.

If there were 38 employees at $100 per employee for the initial investment, there wouldn't be a problem because it would total $3800. However, with 15 employees, it is only $1500. Need it to be a minimum of $3500.

If you could take my existing formula and add the function that says if the result is less than 3500, it's 3500, that would be most helpful.

 

[wsjackman]

=max(3500,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)))))))*'Assurance Offerings'!$B$7)

 

[lcfierro]

you're a genius!

Thanks so much