Hello, the issue I have here is that with a certain formula, most cells will return a number, however some are going to be errors that show "#VALUE!" in the cell. I just want these cells to return blank. I was able to get this working with VLOOKUP cels but not with a simple formula.
Here's the formula I am using: =(Q2+R2+U2)/3
I am using cell N2 to enter this formula into in the example. So I tried using this for N2 entry: =IF((Q2+R2+U2)/3,N2,("")
Thanks for any help, I appreciate it!
Hello,
In case you want to put an error trap like that, you can use
Please Login or Register to view this content.
(copy pasta from Ford)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
Regards,
Lem
Try this...
For 2003 version
Formula:
Please Login or Register to view this content.
For 2007 and above version
Formula:Please Login or Register to view this content.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
I think the OP is using Excel 2003, that's why I have to used ISERROR instead.
Thanks to you both, they worked as expected and I see where my mistake was made.
I need to update that, I didn't realize the default set it to that. Thanks for mentioning that as well, I left some reputation points for you both.Originally Posted by Lemice
@ Lemice,
Yes, I seen it and added the formula for 2003 too...I thought to remove my post but I seen that you missed to suggest (Q2+R2+U2) the Sum function SUM(Q2,R2,U2). In that way the addition will result error when there is any Text data present in those cells and by default the function will result nothing due to error in addition area. So it's better to use sum function which will ignore the text data's.
In case you use SUM, then the intention of using an Error Trap is meaningless, because SUM will always return a number value anyway (Like, 0 for the case of 3 texts / 3 blanks / mix of text and blank).
Yes exactlybut I believe it won't be an issue. Any how when the divisor is 0 or refers to text value then it will result error.
Please correct me if I am wrong
Yes you are right, because #DIV/0 will be the only error left that will trigger the IFERROR or ISERROR when using SUM.
I think the OP wanted it that if it's error in any case, it will be blank (not enough data to calculate), like to only get a result when all 3 numbers are present, if any of them is missing, then return a blank. In a case like that, SUM will not return a #VALUE! error, triggering the if FALSE part of ISERROR and IFERROR. Furthermore, the divisor is fixed at 3.
In any case, the OP found a solution to his problem and marked this thread as solved anyway.
Thanks for the understanding and brief
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks