14 Replies Latest reply on Mar 16, 2012 12:56 PM by SeamusDoone

# IF function not working correctly

### Title

IF function not working correctly

### Post

Hello,

I have an IF function that is revelaing the results of a trend for invoices in consecutive years.  I want the result to either be a (+) or a (-).

I basically have 3 years in a row: Year1, Year2, Year3

My current equation looks like this =IF(Year1<Year2<Year3, "(+)", "(-)")

I want it to be an ascending equation where if 2009 is more than 2010 or 2011 then a negative trend is the result

if 2009 is less than 2010 which is less than 2011 then I want it to be a (+) trend

if 2009 is less than 2010 but 2010 is more than 2011 I want a (-) trend

What does the equation have to look like if thats possible?

• ###### 1. Re: IF function not working correctly

If ( Year1 < year2 and year2 < year3 ; "(+)" ; "(-)" )

• ###### 2. Re: IF function not working correctly

That did not work.  For instance, one incorrect answer I got is (year1=1, year2=0, year3=3) and the result is (-).  I want this result to be (+), any ideas?

• ###### 3. Re: IF function not working correctly

Ok all the records that have a number in 2009 and 2011 and no number in 2010 has a (-) result.  Some are correct while others are incorrect.

• ###### 4. Re: IF function not working correctly

Hmmm, first make sure that your year fields are of type number. Then add some parenthesis:

If ( ( Year1 < year2 ) and ( year2 < year3 ) ; "(+)" ; "(-)" )

• ###### 5. Re: IF function not working correctly

That is still not working, I am getting the wrong answer for all variations.  Some answers are correct and others are not.  I have updated the calculation by changing the type to number and then back to calculations.

• ###### 6. Re: IF function not working correctly

Make sure your calculations specify "number" and not "text" in the return type drop down located in the lower left corner of the specify calculation dialog.

My tests with this demo file indicate that the added parenthesis are not needed: http://www.4shared.com/file/4hytoc0q/IfDemo.html

• ###### 7. Re: IF function not working correctly

Yes, I have made sure of that.  I also have downloaded your file and seen that my equation is the same as yours not sure what is going on or what is wrong with the calculation.

• ###### 8. Re: IF function not working correctly

What is the calculation?

• ###### 9. Re: IF function not working correctly

=IF(year1<year2 and year2<year3, "(+)","(-)")

Is there a way of making a nested IF function that would work with this?

• ###### 10. Re: IF function not working correctly

The problem is that if the function has a year 2 value that is less than the year1 function and the year1 function is less than the year 3 I get a (-) sign instead of a (+) sign because it is only indicating the first part of the equation and producing a result.  This equation does not account for the second part of the equation once that is found.

• ###### 11. Re: IF function not working correctly

I wanted the calculations that define the three year fields.

The problem is that if the function has a year 2 value that is less than the year1 function and the year1 function is less than the year 3 I get a (-) sign

That doesn't make any sense to me. The expression IS evaluating correctly, but isn't producing the results you want.

Why should values of year 1 = 100, year 2 = 50 and year 3 = 200 produce a result of (+)?

Both the current expression and (in math text book terms) your original expression SHOULD produce a result of (-), not (+).

If only year 1 and year 3 values matter, your expression should be:

IF(year1<year3, "(+)","(-)")

• ###### 12. Re: IF function not working correctly

The reason I have to include the second year is because if the value in the second year is more than year1 and year3 then there should still be a (-).

• ###### 13. Re: IF function not working correctly

Then we need a more sophisticated expression.

If ( year 1 < Year3 AND year 2 < year 3 ; "(+)" ; "(-)" )

• ###### 14. Re: IF function not working correctly

That worked thank you very much