# #VALUE! error

#### MIyoshi_Pon

##### New Member
I am using the below formula for a sales calculator in my organization and am getting a #VALUE ! error. Once I fill in D12 the error goes away, but if D12 is blank, the error is there. I'm sure it's a IFERROR I need to put in the formula, but not sure where to enter and compete the formula to get it to work.

I appreciate any help.

=IF(C12="","",IF(SUM(C12-D12)<=0,"Achieved",(SUM(C12-D12))))

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Rick Rothstein

##### MrExcel MVP
I am using the below formula for a sales calculator in my organization and am getting a #VALUE ! error. Once I fill in D12 the error goes away, but if D12 is blank, the error is there. I'm sure it's a IFERROR I need to put in the formula, but not sure where to enter and compete the formula to get it to work.

I appreciate any help.

=IF(C12="","",IF(SUM(C12-D12)<=0,"Achieved",(SUM(C12-D12))))
What should the answer be if cell D12 is blank... a blank or should it be treated as 0 and return C12?

By the way, you do not need either SUM function call as you are not summing anything... C12-D12 is a direct calculation which does not need the SUM function in order to perform it.

#### MIyoshi_Pon

##### New Member
Rick,

Thank you for your response and feedback on the SUM. To answer your question, D12 is the MTD number that is entered based on sales performance MTD. So if sales MTD are zero it could be 0 or blank.

#### Rick Rothstein

##### MrExcel MVP
Thank you for your response and feedback on the SUM. To answer your question, D12 is the MTD number that is entered based on sales performance MTD. So if sales MTD are zero it could be 0 or blank.
Okay, I just looked at your formula in detail and I do not see why you are getting a #VALUE ! error when D2 is blank... I get the value in C12 when D2 is blank. Now, from your description, this is not the value you want, but I think we need to understand why you are getting an error with your current formula. Are you sure the D12 is blank? I ask because if you have a space character in the cell, that would look like the cell is blank, but because a space is an actual text character, that would make your formula error out. Do you have a space in D12 or, alternately, a formula which displays a space (" ") character for some tested condition?

#### MIyoshi_Pon

##### New Member

Rick,

I do have a formula in D12:

=IF(B\$31="","",SUM(C31:C45,G31:G45))

The cells in the above formula for the SUM are blank.

##### Well-known Member
Are you sure the D12 is blank?

The cells in the above formula for the SUM are blank.

Be extra sure. Check with the =isblank() function. If you imported from a database then there might be invisible blanks (""). Even if there are no spaces (len = 0). Notice that "" is greater than zero but blanks are not:

Last edited:

#### MARK858

##### MrExcel MVP

On top of the questions asked in post number 6. What is in C12? in other words the results of the tests sheetspread has posted on C12 well as D12 if it is blank, if it is not blank what does the formula =ISNUMBER(C12) return?

Last edited:

##### Well-known Member
In addition to agreeing with Mark's comments I'd like to repeat what Rick said about not needing the sum() parts.

Last edited:

##### MrExcel MVP
@ MIyoshi_Pon

1.

=IF(C12="","",IF(C12-N(D12)<=0,"Achieved",C12-N(D12)))

2.

=IF(ISNUMBER(1/C12*D12),IF(C12-D12<="Achieved",C12-D12),"")

Replies
4
Views
332
Replies
0
Views
48
Replies
6
Views
159
Replies
5
Views
21
Replies
5
Views
56

1,136,435
Messages
5,675,850
Members
419,587
Latest member
leronardo092004

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back