Forum Discussion
Rosa Hernandez
Oct 04, 2017Copper Contributor
Excel Formula, combining two IF statements
I need to string together two IF statements, =IF(C7>=70, C7*0.5) and =IF(C7<=69, C7*0.65), please help
SergeiBaklan
Oct 10, 2021MVP
You current formula has IF() only in it first part
=IF( B7 > 5, (C7+D7+E7)*30/365*(B7-5) ) +
(C7+D7+E7/2)*30/365*2 +
(C7+D7)*21/365*3
If you mean combine second formula with that part it could be
=IF( B7 > 5, (C7+D7+E7)*30/365*(B7-5),
IF( B7 > 3, "have no idea what shall be here",
(C7+D7)*21/365*B7
)) +
(C7+D7+E7/2)*30/365*2 +
(C7+D7)*21/365*3
It is not defined what shall be returned if B7 is between 3 and 5. With assumption it is second part of your initial formula, it could be
=IF( B7 > 5, (C7+D7+E7)*30/365*(B7-5),
IF( B7 > 3,
(C7+D7+E7/2)*30/365*2 +
(C7+D7)*21/365*3,
(C7+D7)*21/365*B7
))
Dubai_Red
Oct 10, 2021Copper Contributor
thanks Sergei.
B7 = years and I have different variables depending on the number of years a person is in employment.
so if a person is in employment for 8 years, the remuneration would be split in different parts for service completed between:
years 5 to year 8,
year 3 to years 5
years 1 to 3.
the first IF function works for anyone who has completed more than 5 years of service as well as anyone who has completed more than 3 years. It’s only when the person has completed less than 3 years I get stuck.
the 2nd IF function works in isolation, but not when combined with the 1st.
- SergeiBaklanOct 10, 2021MVP
To split on part
years 5 to year 8,
year 3 to years 5
years 1 to 3
with nested IF could be done as
=IF( B7 >= 8, calculationEight, IF( B7 >= 5, calculationFive, IF( B7 >= 3, calculationThree, IF( B7 >= 1, calculationOne, 0 ))))