#VALUE! error

MIyoshi_Pon

New Member
Joined
Jul 15, 2018
Messages
3
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
Joined
Apr 18, 2011
Messages
37,154
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Joined
Jul 15, 2018
Messages
3
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
Joined
Apr 18, 2011
Messages
37,154
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Joined
Jul 15, 2018
Messages
3

ADVERTISEMENT

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

Well-known Member
Joined
Sep 19, 2005
Messages
5,120
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:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";"></td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style=";">a</td><td style="text-align: right;;"></td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";"></td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;">FALSE</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet6</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</th><td style="text-align:left">=""</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=A2>0</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=ISBLANK(<font color="Blue">C2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A6</th><td style="text-align:left">=""</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A8</th><td style="text-align:left">=SUM(<font color="Blue">A1:A7</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C8</th><td style="text-align:left">=SUM(<font color="Blue">C1:C7</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,140
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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:

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,120
In addition to agreeing with Mark's comments I'd like to repeat what Rick said about not needing the sum() parts.
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
@ 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),"")
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top