Search code examples
excelif-statementexcel-formulaexcel-2007

'The formula you typed contains an error' in Excel Nested IF formula

I'm trying to create an excel formula for the below logic:

    If ((Value in A1 - 250000) >= 250000){
          Then Value in A2 = (0.05*(250000))
       }
    Else{
       If ((Value in A1 - 250000) <= 0){
           Then Value in A2 = 0
       Else{
           Value in A2 = (0.05*(Value in A1-250000))
       }
    }

Below is the formula I wrote:

    =IF(((A1-250000)>=250000), ((0.05*(250000))), IF (A1-250000)<=0, 0, (0.05*(A1-250000)))

And Excel throws following error

    The formula you typed contains an error

Any help with identifying the error is appreciated.

Solution

  • I think this reproduces your logic:

    =IF(A1>500000,0.05*250000,0.05*MAX(A1-250000,0))

    or maybe even:

    =0.05*MAX(0,MIN(A1-250000,250000))

    although I agree that IFS might be more clear, but I think you need Office 365 for that function.