Microsoft Excel 2010 - complete product...

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!

✔ 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

message edited by mmcconaghy

Your getting the #VALUE error because one of the cells appearsblank,

but it probably contains a Space Character.On a blank cell click the Delete Key and see if it helps.

MIKE

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

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 SpaceNot

=IF(XX=1,1,

" ") < SpaceMIKE

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

message edited by mmcconaghy

Ask Your Question

Weekly Poll