Multiple IF formula functions help

NUppal

New Member
Joined
Aug 4, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hi,

I need help with the if formula. I got a basic formula down but now I need to add multiple functions to it and am getting stuck.

This was the formula I was able to get for column CN:

=IF([@[Years of Service]]*[@[Severance Weeks per Policy]]>[@[Min Weeks]],[@[Years of Service]]*[@[Severance Weeks per Policy]],[@[Min Weeks]])

If CJ times CL is greater than CM, than CN should be CJ times CL. If not it should equal to CM.

The problem is column CN is supposed to max out at 54. No one can have more then 54 employee severance weeks, and no one can have less then the minimum number of weeks.

So I am trying to write:

If CJ * CL > CM, than CN = CJ * CL. If not, CN = CM and if CJ*CL>54, CN = 54. If not, CN = CJ*CL except if it is less then CM.

1630691366970.png
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=MAX(54,IF([@[Years of Service]]*[@[Severance Weeks per Policy]]>[@[Min Weeks]],[@[Years of Service]]*[@[Severance Weeks per Policy]],[@[Min Weeks]]))
 
Solution

NUppal

New Member
Joined
Aug 4, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
I've tried that but doesn't work. The output is still 106.21 (CJ*CL)

1630692677765.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
Oops not thinking. It should be Min not Max.
 

NUppal

New Member
Joined
Aug 4, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
thank you so much!! it worked :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,147,743
Messages
5,742,936
Members
423,765
Latest member
PaulD1984

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top