Complex IFS Formula Issue

[robatthe2A]

Good Morning-

Having some trouble with this equation where I have the calculation set correctly based on the criteria, but I need it to adjust if the annual balance goes below 80 hours. Once below 80 hours in column F I need column H to never be above column F and only deduct if the code entered is applicable based on the parameters set.

=IFS(ISBLANK(D15),"",F14-B15<(--"80:00"),F15,D15="Paid Time Off",H14-0,D15="Paid Sick and Safe Time",H14-B15,D15="Bereavement Paid",H14+0,D15="Civic Duty",H14+0,D15="Emergency Leave - Disaster Area Paid",H14+0,D15="Emergency Leave - Non Disaster Area Paid",H14+0,D15="FMLA Unpaid",H14+0,D15="Intermittent FMLA Paid Time Off PSST",H14-B15,D15="Intermittent FMLA No Pay",H14+0,D15="Intermittent FMLA Paid Time Off PTO",H14-B15,D15="STD Elimination Period Paid PTO",H14-B15,D15="STD Elimination Period - Unpaid",H14+0,D15="STD Elimination Period - Paid PSST",H14-B15,D15="STD Elimination Period Paid PTO",H14-B15,D15="Short Term Disability 70% Paid",H14+0,D15="Short Term Disability 100% Paid",H14+0,D15="Partial STD 70%",H14+0,D15="Partial STD 100%",H14+0,D15="LOA Pending Unpaid",H14+0,D15="LOA Pending Paid",H14-0,D15="Denied LOA",H14+0,D15="Leave as Accommodation",H14+0,D15="Military Leave Active Duty Paid",H14+0,D15="Military Leave Active Duty Unpaid",H14+0,D15="Military Leave Non Active Duty Paid",H14+0,D15="Military Leave Active Duty Paid",H14+0,D15="Paid Early Release",H14+0,D15="Parental Leave Paid",H14+0,D15="Pending Return to Work",H14+0,D15="Personal Leave Paid",H14+0,D15="Personal Leave Unpaid",H14+0,D15="Parental Intermittent Paid",H14+0,D15="Restricted Emergency Time",H14+0,D15="State PFML Unpaid",H14+0)

 

[DanteAmor]

Hi and welcome to MrExcel.

You could reduce your formula to the following:

=IFS(D15="","",F14-B15<(--"80:00"),F15,COUNTIF(AA:AA,D15),H14+0,COUNTIF(AB:AB,D15),H14-0,COUNTIF(AC:AC,D15),H14-B15)

But you will have to separate into columns when you want H14+0 or H14-0 or H14-B15, (I don't understand why you have H14+0 and H14-0, the result is the same, but I put it in separate columns anyway).
This way you can add more concepts to the formula in a simpler way.

varios 27dic2024.xlsm
	A	B	C	D	E	F	G	H																		Z	AA	AB	AC
10
11						8																					H14+0	H14-0	H14-B15
12																											Bereavement Paid	Paid Time Off	Paid Sick and Safe Time
13																											Civic Duty	LOA Pending Paid	Intermittent FMLA Paid Time Off PSST
14						100		20																			Emergency Leave - Disaster Area Paid		Intermittent FMLA Paid Time Off PTO
15		12		Paid Sick and Safe Time		50																					Emergency Leave - Non Disaster Area Paid		STD Elimination Period Paid PTO
16																											FMLA Unpaid		STD Elimination Period - Paid PSST
17																											Intermittent FMLA No Pay		STD Elimination Period Paid PTO
18																											STD Elimination Period - Unpaid
19																											Short Term Disability 70% Paid
20																											Short Term Disability 100% Paid
21																											Partial STD 70%
22																											Partial STD 100%
23																											LOA Pending Unpaid
24																											Denied LOA
25																											Leave as Accommodation
26																											Military Leave Active Duty Paid
27																											Military Leave Active Duty Unpaid
28																											Military Leave Non Active Duty Paid
29																											Military Leave Active Duty Paid
30																											Paid Early Release
31																											Parental Leave Paid
32																											Pending Return to Work
33																											Personal Leave Paid
34																											Personal Leave Unpaid
35																											Parental Intermittent Paid
36																											Restricted Emergency Time
37																											State PFML Unpaid
Hoja1

Cell Formulas
Range	Formula
F11	F11	=IFS(D15="","",F14-B15<(--"80:00"),F15,COUNTIF(AA:AA,D15),H14+0,COUNTIF(AB:AB,D15),H14-0,COUNTIF(AC:AC,D15),H14-B15)

Now, returning to your original question and with a simplified formula. Explain with examples what you need.

You can give the examples using minisheets, as I have done, using XL2BB tool.

Note XL2BB:

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in​

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.​

----- --
I hope to hear from you soon.
Respectfully
Dante Amor
----- --