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!

✔ 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

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 goReading 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

OK, there is more than one thing going on here. First, you say you have the formula in

P4and 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

numberzero 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.

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)=60However 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!

As I said in reply # 1

If you want aZero,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)=60There is no need for the Plus Signs,

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

MIKE

Thank you for such a quick response! Worked like a charm - OMG thank you!!!

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?

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

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History