if statement return a third value

[buzz71023]

i just started work with the if function again and didnt know much to begin with but I have the statement below which works but I would like for the statement to return a third value. The other condition I have is "if c19=c18 then result is 0". hopefully thats all the info needed. if not let me know and I will do my best to get/explain whatever else is needed.

I realize I might have to use a different function with this or a different one all together but I am not very good with functions yet.

Thanks

=IF(C19>C18, 10, -9)

 

[Robert Mika]

=if(c19>c18, 10, if(c19=c18,0,-9))

 

[Mindpsyche]

Try

=IF(C19>C18,10,IF(C19=C18,0,-9))

Robert beat me

 

[buzz71023]

Thanks... Perfect

 

[francomusso]

The IF statement above works perfectly, but just thought I'd contribute a bit of an explanation to help understand why it works / how it was written.

A simple if statement such as the one you originally posted goes as follows: =IF(condition to be checked,what to do if check returns true,what to do if check returns false)

For further checks, you basically use another if statement in place of the 'what to do if check returns false' part, like so...

=IF(first condition to be checked,what to do if first check returns true,IF(second condition to be checked, what do do if second check returns true

...and then finish with what to do if none of those checks return true *AND CLOSE ALL OPEN BRACKETS* like so...

=IF(condition to be checked,what to do if check returns true,IF(second condition to be checked, what do do if second check returns true, what to do if all checks return false))

You could string together lots of checks this way (only up to 7 if you're still stuck in Excel 2003 like I am!) by continuing to add further ifs before the one 'what to do if all checks return false part.

One word of caution though...
As soon as one check returns true, Excel will stop checking. Consider an example where you need to assign classify the sale as "Low" if A1 is greater than 1,000, "Medium" if A1 is greater than 5,000 or "High" if A1 is greater than 10,000.

Written as =IF(G19 > 1000,"Low",IF(G19 > 5000,"Medium",IF(G19 > 10000,"High","Misc")))
there would be a problem as a value of 20000 should be classified as "High" but as it is greater than 1,000 Excel would class that as a match, classify it as "Low" and stop checking.

The trick is to check for the highest value first. Here that would mean check if it's over 10,000 if not check if it's over 5,000 and if not finally check if it's over 1,000 like so:
=IF(G19 > 10000,"High",IF(G19 > 5000,"Medium",IF(G19 > 1000,"Low","Misc")))

I hope that helps.
All the best,
Franco Musso