Solved #Value! is preventing my formula from working.

Microsoft Excel 2010 - complete product...
March 11, 2016 at 14:02:14
Specs: Windows 7
The Formula I'm running is:

=(D4*0.225)+(F4*0.225)+(H4*0.1)+(J4*0.1)+(L4*0.1)+(N4*0.25)

When any of the cells have a value that is blank which will happen. I get a result of Value. I need the formula to calculate the cells available and make the calculation or if all cells are blank leave the cells blank. Please help!


See More: #Value! is preventing my formula from working.

Report •


✔ Best Answer
March 11, 2016 at 15:39:26
You will probably also have to modify your formula to use the SUM function,
which ignores Text Characters.

Something like this should work

=SUM(D4,F4)*0.225+SUM(H4,J4,L4)*0.1+SUM(N4)*0.25

See here for more info on the #VALUE error

https://support.office.com/en-us/ar...

EDIT ADDED

This should also work:

=SUM(SUM(D4,F4)*0.225,SUM(H4,J4,L4)*0.1,SUM(N4)*0.25)


MIKE

http://www.skeptic.com/

message edited by mmcconaghy



#1
March 11, 2016 at 14:42:46
Your getting the #VALUE error because one of the cells appears blank,
but it probably contains a Space Character.

On a blank cell click the Delete Key and see if it helps.

MIKE

http://www.skeptic.com/


Report •

#2
March 11, 2016 at 14:51:05
The blank cells is pulling data from my other excel folders. Sometimes date is missing so it needs to be ignored till we have something to input. I need to tell the formula to ignore the cell somehow.

message edited by Ravingoofy


Report •

#3
March 11, 2016 at 15:22:31
What is the formula that is in the Blank cell?

If you are using double quotes to indicate a null cell,
make sure there is NO space between the quotes.

The formula should look like:

=IF(XX=1,1,"") < No Space

Not

=IF(XX=1,1," ") < Space

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
March 11, 2016 at 15:39:26
✔ Best Answer
You will probably also have to modify your formula to use the SUM function,
which ignores Text Characters.

Something like this should work

=SUM(D4,F4)*0.225+SUM(H4,J4,L4)*0.1+SUM(N4)*0.25

See here for more info on the #VALUE error

https://support.office.com/en-us/ar...

EDIT ADDED

This should also work:

=SUM(SUM(D4,F4)*0.225,SUM(H4,J4,L4)*0.1,SUM(N4)*0.25)


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

Ask Question