I have three column of data.
One example: 2500, #n/a, #n/a (say a1,b1,c1)
I am using 'if' formula when any of the data consist of number not #n/a it would result of 0.
My formula is =if(or(a1>0,b1>0,c1>0),0,#n/a) and it show #n/a.
When I separate the formula is ok. =if(a1>0,0,#n/a) shows 0 and the other two show #n/a.
Any idea what went wrong?
Maybe this...
=IF(COUNT(A1:C1),0,#N/A)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
hi david. the reason is because the formula comes to a halt when it encounters error. your logical tests for each of them are supposed to return TRUE or FALSE
A1>0
B1>0
C1>0
for eg. if you select this part in the formula A1>0 & press F9 to calculate, it shows you TRUE
but if you select B1>0 & press F9, it becomes #N/A and not FALSE. once it has an error, your results become an error.
so you have to use some formulas that can ignore the error & give you a non-error/TRUE/FALSE like Tony's COUNT. COUNT will tell you how many numbers are in A1:C1. if it's 1 or more, it returns 0. otherwise, it returns #N/A
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Thanks you. It's work.Originally Posted by Tony Valko
hi benishiryo,
thank you for the explanation. it's important to me not only what work but how it works.
Hi,
An alternative.
=IF(AND(ISERROR(A1),ISERROR(B1),ISERROR(C1)),"#NA",0)
This will works even a cell has no value/figure.
Last edited by reimar_rem; 12-21-2013 at 12:10 AM.
Click * below if this answer helped
You're welcome. Thanks for the feedback!
If your question has been answered please mark the thread as being solved.
In the menu bar above the very first post select Thread Tools>Mark this thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks