Solved #NAME error - calling data from cell w/IF formula with text

June 14, 2016 at 10:30:28
Specs: Windows 64
Hi,

I have an IF formula in cell P4 =IF(ISBLANK(N4),"",IF(N4*30>=100,"0",N4*30))

N4*30>=100,"0" - Excel is reading the "0" as, So when I go to cell AF4 I am getting the #Name error. I must have P4 populate a 0 in order to get an average off of the total in this column.

Please help!


See More: #NAME error - calling data from cell w/IF formula with text

Report •


✔ Best Answer
June 14, 2016 at 13:50:11
Again, not sure what your looking for, but if you want to determine if
100 minus the sum of cells P3, T3, X3 is equal to 60 then something like:

=IF(100-SUM(P3,T3,X3)=60,"OK","NOT OK")

How does that work for you?

MIKE

http://www.skeptic.com/



#1
June 14, 2016 at 12:16:11
Your post is a bit confusing, seems like you forgot something here:

N4*30>=100,"0" - Excel is reading the "0" as, So when I go

Reading the "0" as WHAT?

But, putting quotes around a Zero, makes it TEXT not a Number.

If you want a Zero, remove the quotes:

=IF(ISBLANK(N4),"",IF(N4*30>=100,0,N4*30))

MIKE

http://www.skeptic.com/


Report •

#2
June 14, 2016 at 12:19:57
OK, there is more than one thing going on here.

First, you say you have the formula in P4 and then you say "when I go to cell AF4 I am getting the #Name error.".

I don't see how AF4 is related to this. Does AF4 reference P4? Keep in mind that we can't see your spreadsheet from where we are sitting, so we don't know how, or even if, AF4 relates to your problem.

Second, if you want the number zero in a cell, do not use the quotes. When you enclosed something in quotes, Excel considers it to be a text value.

Perhaps the quotes are the root cause of your #NAME error.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
June 14, 2016 at 13:00:52
My apologies - I am new to the Excel and fomatting world. I'll try to explain what I need again.

In P4 (total number of deducted points I am using the IF(ISBLANK formula to get the total number of points being deducted from each work item with the maximum achievable points of 100 (the # of errors is listed in N4). 100 * 30 (ie 2 errors, deduct 30 points each). If the final score (once 60 points is deducted from 100) if the score is 100 or a negative value (ie -20) then the work item will score 0 points (hence the "0") this is necessary to get the average of all approved work item for monthend. Also if there is no errors in one particular catergory the cell should remain blank.

=IF(ISBLANK(N4),"",IF(N4*30>=100,"0",N4*30))

Once Excel has calulated P4*30 and the other two categoris (errors are based on severity) the other two results (final score) can be found in cells T3 and X3 (all three are P3, T3,X3)


Now in cell AF4 I am adding the final scores for all three categories (P3, T3,X3) to be subtracted from 100. (ie 100-SUM(P3+ T3+X3)=60

However I am running into the #Name error because the 0 in the IF(ISBLANK formula is being read as text.

I hope this clears up your question - I appreciate any help I can get. Thanks!


Report •

Related Solutions

#4
June 14, 2016 at 13:19:08
As I said in reply # 1

If you want a Zero, remove the quotes:

=IF(ISBLANK(N4),"",IF(N4*30>=100,0,N4*30))


Additionally, in your subtraction formula, you show:

100-SUM(P3+ T3+X3)=60

There is no need for the Plus Signs,

100-SUM(P3,T3,X3)=60

MIKE

http://www.skeptic.com/


Report •

#5
June 14, 2016 at 13:24:49
Thank you for such a quick response! Worked like a charm - OMG thank you!!!

Report •

#6
June 14, 2016 at 13:34:19
Well I spoke to soon. Now the formula is returning a 100 value (formula is correct but is not what I am looking to get).

100-SUM(P3,T3,X3)=60 (OK)

Lets consider the 100-SUM(P3,T3,X3)=0, then the answer will be 100. I need for it to return 0 since this work item will not get credit BUT those cases which received points will receive the correct points. I believe its a IF formula?


Report •

#7
June 14, 2016 at 13:50:11
✔ Best Answer
Again, not sure what your looking for, but if you want to determine if
100 minus the sum of cells P3, T3, X3 is equal to 60 then something like:

=IF(100-SUM(P3,T3,X3)=60,"OK","NOT OK")

How does that work for you?

MIKE

http://www.skeptic.com/


Report •


Ask Question