Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Well, this is a simple question really.

In Excel 2010, why isn't the =IF function working? What I am typing in is:

=IF(OR(G5:W13=20),"","$20")

What I am trying to do is if any cell between the area of G5 and W13 has the exact value of $20 (all of the cells are set to text format Currency, before anyone corrects me) it will show nothing. Though if there isn't any cells that have the value of $20, it will just show $20 on that particular cell.

After experimenting, the culprit is it saying G5:W13. I'm okay typing all the cells between that area, so I did do that. However, it tells me that I have an error in my formula when I do that, so therefore I am stuck.

Is there a way from it to stop correcting either of those? All help is appreciated. :)

share|improve this question
    
This might be helpful: excelforum.com/excel-formulas-and-functions/… –  ta.speot.is Apr 21 '13 at 8:09
    
I'm sure the error is not "it does not work". As you only have a single parameter in the OR function (rather than some OR(condition1, condition2, condition3, ...)): did you use an array formula (Ctrl+Shift+Enter)? (And as an aside: you might not want to include the $ if you already formatted all cells as Currency.) –  Arjan Apr 21 '13 at 8:45
1  
Yes, I have replaced the =OR() with =COUNTIF, and it is working like a charm. Thank you so very much. :) Oh, and just to clarify, Arjan, the one that had the $ at the front wasn't in currency. The first 20 was. :) –  Mordecaii Apr 21 '13 at 9:43
1  
@w3d, the OR can be used to find a value in an array, if entered using Ctrl+Shift+Enter. OpenOffice.org will then display it with curly braces, as {=IF(OR(G5:W13=20),"","$20")}; not sure about Excel. (Not that such is the best solution for this problem.) Mordecaii, why not format the result cell as Currency as well, and output the number 20 rather than some text "$20"? Chances are that one day you might want to use that result in some other calculations. Don't use text where it's not really text. –  Arjan Apr 21 '13 at 10:14
1  
Wow, as for outputting a number rather than some text like I commented above: there you have it... –  Arjan Apr 21 '13 at 10:53
show 4 more comments

1 Answer

Use the following formula:

=IF(COUNTIF(G5:W13, 20), "", "$20")

This counts the number of instances of 20 in the desired range, if greater than zero it will show "$20"

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.