We’ve posted a new Expert Spotlight! Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

I have a field in an excel sheet that is giving me #value as the result of a formula. I was wondering if someone could decode this for me.

=E20*$E$2+E21*$E$3+E22*$E$4

=E20*$E$2+E21*$E$3+E22*$E$

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial=SUMPRODUCT($E$2:$E$4*E20:

cheers, teylyn

E2 is 744

E21 is 110.2

E3 is 672

E22 is blank

E4 is 744

So I am guessing the blank is the issue?

=IF(<condition>,<something

or

=IF(<condition>,"",<someth

and the empty string is returned, then, yes, that's the reason for the error. In this case, change the empty string to a 0, like

=IF(<condition>,<something

or

=IF(<condition>,0,<somethi

and your sum will calculate correctly (assuming that all other numbers are true numbers, and not numbers stored as text. Posting an Excel file with your sample data would be a much better way of evaluating your data types.)

cheers, teylyn

It will work.

You're multiplying with Null (E22=Blank). Excel cannot perform the calculation.

Replace the empty cell with 0 and the formula will work.

In Excel, cells prefixed with $ (e.g. $E$4) will stay the same if you copy the formula to another row/column).

So when you copy /paste the formula to another Column (let's say Q) this formula : E20*$E$2+E21*$E$3+E22*$E$4

becomes

Q20*$E$2+Q21*$E$3+Q22*$E$4

goodluck, .Frans.

sorry, but I don't see how a new worksheet would make a difference. The underlying problem of data type stays the same, regardless of where the formula is executed.

>> It will work.

Can you substantiate this with an example? Please back up your claims with working examples, especially if you are just guessing.

=IF(SUM(IF('Business Objects_Data'!F202:F213="0

and the data it is referenceing on F202:F213 is as follows

Month

01

02

03

04

05

06

07

08

09

and P202:P213 is

Actual Quantity

149,392.00

73,910.00

0.00

0.00

0.00

0.00

0.00

0.00

0.00

The formula you are posting now is a conditional sum as an array formula. Are you entering it as an array formula, i.e. with Ctrl-shift-enter

Re confidential business data: How hard can it be to just create a copy of your file, delete all data but the cells you want to use in the calculations, so there's no confidentiality issue, since we do not see any client/address/email/whatn

Even if your text values are client names, how hard can it be to replace 20 cells with dummy data? Name1, Name2, Name3 or Product1, Product2, Product3 .... You get the idea?

It would be most helpful to see an Excel file with your expected results mocked up manually and your explanation of the logic that leads to those results.

cheers, teylyn

Your latest post is a completely new question. Remember, as a Premium Service Member, you have unlimited points. Use them to attract more experts to a new question.

cheers, teylyn

=IF(SUM(IF('Business Objects_Data'!F202:F213="0

hope it'll work.

=E20*$E$2+E21*$E$3+E22*$E$

and the next formula is the formula tha is in E22 that is blank

=IF(SUM(IF('Business Objects_Data'!F202:F213="0

sorry, but I fail to see how relative/absolute referencing relates at all to the issue of the question.

please do not dish out your peace meal. State the whole problem. Attach a sample file that illustrates the issue. You are sending several experts on a guess hunt. Do your bit, please and at least give us some concrete info to work with.

cheers, teylyn

was meant to read:

please do not dish out your question peace meal.

=IF(SUM(IF('Business Objects_Data'!F202:F213="0

Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

hard to tell without seeing your data

Most likely, one of the values in the cells referenced by the formula is text, not numbers, so when used in a mathematical operation, they will throw an error. Make sure that all cells E20, E2, E21, E3, E22 and E4 are really numbers and not just numbers stored as text.

cheers, teylyn