Forum Discussion

Profowo's avatar
Profowo
Copper Contributor
Jul 27, 2023

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

SergeiBaklan's avatar
SergeiBaklan
MVP
Jul 27, 2023

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.

  • pbasu92's avatar
    pbasu92
    Copper Contributor
    Nov 09, 2023

    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.

      • pbasu92's avatar
        pbasu92
        Copper Contributor
        Nov 12, 2023
        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.

Resources