Excel Formula, combining two IF statements

[Rosa Hernandez]

I need to string together two IF statements, =IF(C7>=70, C7*0.5) and =IF(C7<=69, C7*0.65), please help

[#2]

@SergeiBaklan 

Hi Segei

Can you help me with combining the below two IF statements:

Hi Everyone, 

I need to combine two IF statements:

IF(B7>5,(((C7+D7+E7)*30/365))*(B7-5))+((C7+D7+(E7/2))*(30/365)*2)+(((C7+D7))*(21/365)*3)

IF(B7<=3,(C7+D7)*(21/365))*B7

Any suggestions would be highly appreciated. 

Thanks

[#3]

@Dubai_Red 

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

[#4]

@SergeiBaklan 

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.

[#5]

@Dubai_Red 

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

[#6]

@Rosa Hernandez 

 The two if statements you think you need are really just one:
Here's an outline of a basic IF

=
IF(
logical test,
perform this IF TRUE,
perform this IF FALSE
)

Here's your initial restated:

=
IF(
C7>=70,
C7*0.05,
C7*0.65
)

[#7]

Sir I am having error with the below formular, I need a solution

=IF(E2<300000,0.07*E2,0.07*300000),IF(E2-600000>=500000, 0.15*500000,IF(E2-600000<0,"-",0.15*(E2-600000),IF(E2-600000>=500000, 0.15*500000,IF(E2-600000<0,"-",0.15*(E2-600000),IF(E2-1100000>=500000, 0.19*500000,IF(E2-1100000<0,"-",0.19*(E2-1100000),IF(E2-1600000>=1600000, 21%*1600000,IF(E2-1600000<0,"-",21%*(E2-1600000),IF(E2-3200000>0,24%*(E2-3200000),"-"))))) E2 =3,021,654.21

[#8]

@Profowo 

There are lot of errors in your formula. If to clean syntax it could be

=IF( E2 < 300000, 0.07 * E2,
 IF( E2 - 600000 >= 500000, 0.15 * 500000,
 IF( E2 - 600000 < 0, 0.15 * (E2 - 600000),
 IF( E2 - 600000 >= 500000, 0.15 * 500000,
 IF( E2 - 600000 < 0, 0.15 * (E2 - 600000),
 IF( E2 - 1100000 >= 500000, 0.19 * 500000,
 IF( E2 - 1100000 < 0, 0.19 * (E2 - 1100000),
 IF( E2 - 1600000 >= 1600000, 21% * 1600000,
 IF( E2 - 1600000 < 0, 21% * (E2 - 1600000),
 IF( E2 - 3200000 > 0, 24% * (E2 - 3200000), "-" )
   ) ) ) ) ) ) ) ) )

but what it shall to calculate is unclear.

[#9]

Hi Sergei, I need help with combining two IF statements:
IF(OR(
AND(ISTEXT(C2),ISTEXT(D2),C2>D2),
AND(ISNUMBER(VALUE(C2)),ISNUMBER(VALUE(D2)),C2>D2)),
D2,C2),
IF(OR(
AND(ISTEXT(C2),ISNUMBER(VALUE(D2)),C2>D2),
AND(ISTEXT(D2),ISNUMBER(VALUE(C2)),C2>D2)),
C2,D2)
Please help. Truly appreciate it.

[#10]

@pbasu92 

Perhaps

=IF( IFERROR(--C2, C2) > IFERROR(--D2, D2), D2, C2)

if I understood the logic correctly

[#11]

Thank you Sergei. But the logic isn't accurate.

Column E should return like this:
C D E
11 2 2
2 11 2
a 2 a
2 a a
a b a
b a a

I need the function to return the lower number if C & D are both numeric and return the lower alpha if C & D are both alpha but when C is numeric & D is alpha or vice versa the function has to return the alpha.

Appreciate it.

[#12]

@SergeiBaklan
I tried working on it , modified the formula you used and managed to make it work.
The formula is this - 

=IF(IFERROR(-C2,D2)>IFERROR(-D2,C2),C2,D2)

Thank you.
I need another favor. I need Column E to return like this in case of a blank cell (the last two rows):
C D E
11 2 2
2 11 2
a 2 a
2 a a
a b a
b a a

  2  2

2    2

Thank you in advance.

[#13]

@pbasu92 

Perhaps

=IF( ISNUMBER(--C2)*ISNUMBER(--D2),
     MIN(--C2, --D2),
     IF( ISNUMBER(--C2), D2,
         IF( ISNUMBER(--D2), C2,
             IF(C2 > D2, D2, C2 ) ) ) )

which returns

[#14]

Thanks a lot Sergei. It works.
I need another favor. I need Column E to return like this in case of a blank cell (the last two rows):
C D E
11 2 2
2 11 2
a 2 a
2 a a
a b a
b a a
2     2
     2 2

Thank you in advance.

[#15]

@pbasu92 

Perhaps

=IF(C2="", D2, IF(D2="",C2, IF(IFERROR(-C2,D2)>IFERROR(-D2,C2),C2,D2) ) )

[#16]

This is Perfect!!!!! Thank you so much! 🙂

[#17]

@pbasu92 , you are welcome