#VALUE! error

[MIyoshi_Pon]

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

 

[Rick Rothstein]

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]

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]

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]

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.

 

[sheetspread]

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:

Excel 2010
	A	B	C	D	E	F
1
2	3		3		TRUE	FALSE
3	7		7		TRUE	FALSE
4	2		a		TRUE	FALSE
5					FALSE	TRUE
6					TRUE	FALSE
7	4		4		TRUE	FALSE
8	16		14		TRUE	FALSE
Sheet6

Cell Formulas
Range	Formula
A1	=""
A6	=""
A8	=SUM(A1:A7)
E2	=A2>0
F2	=ISBLANK(C2)
C8	=SUM(C1:C7)

 

[MARK858]

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?

 

[sheetspread]

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

 

[Aladin Akyurek]

@ 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),"")