What does this excel formula mean?

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
jlcannonAsked:
Who is Participating?
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.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello jlcannon,

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

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
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
by the way, a shorter way to calculate the same result would be with the classic Sumproduct, as in

=SUMPRODUCT($E$2:$E$4*E20:E22)

cheers, teylyn
jlcannonAuthor Commented:
E20 is 200.8
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?
Expert Spotlight: Joe Anderson (DatabaseMX)

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.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
If E22 is truly empty, the formula will work. If E22 has a formula in it that delivers an empty string like

=IF(<condition>,<something>,"")

or

=IF(<condition>,"",<something>)

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

or

=IF(<condition>,0,<something>)

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
ib02012005Commented:
Try using the same formula, i.e., either manual as per your question or the one which is suggested by teylyn, in a new worksheet.

It will work.
koevoetsfjmCommented:
Correct,

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.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
@ib02012005,

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.
jlcannonAuthor Commented:
This is the formula in e22
=IF(SUM(IF('Business Objects_Data'!F202:F213="03", 'Business Objects_Data'!P202:P213, ""))=0, "", SUM(IF('Business Objects_Data'!F202:F213="03", 'Business Objects_Data'!P202:P213, ""))/E$4)

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


jlcannonAuthor Commented:
I would post the excel but it is business data
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
jlcannon, that formula is a completely different formula from the one you posted in your initial question.

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/whatnot information, but just a bunch of text and numbers?

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

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Actually, your initial question has been answered, and then some.

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
ib02012005Commented:
Replace this formula with the existing formula

=IF(SUM(IF('Business Objects_Data'!F202:F213="03", 'Business Objects_Data'!P202:P213,0))=0,0, SUM(IF('Business Objects_Data'!F202:F213="03", 'Business Objects_Data'!P202:P213,0))/E$4)

hope it'll work.
jlcannonAuthor Commented:
The original formula is from the i20 field in my excel sheet that is the formula
=E20*$E$2+E21*$E$3+E22*$E$4

and the next formula is the formula tha is in E22 that is blank
=IF(SUM(IF('Business Objects_Data'!F202:F213="03", 'Business Objects_Data'!P202:P213, ""))=0, "", SUM(IF('Business Objects_Data'!F202:F213="03", 'Business Objects_Data'!P202:P213, ""))/E$4)

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
@Frans,

sorry, but I fail to see how relative/absolute referencing relates at all to the issue of the question.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
jlcannon,

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
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
>> please do not dish out your peace meal.

was meant to read:

please do not dish out your question peace meal.
jlcannonAuthor Commented:
Thanks, I will open another question to get the rest resolved.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
replace the "" in the formula in E22 with 0

=IF(SUM(IF('Business Objects_Data'!F202:F213="03", 'Business Objects_Data'!P202:P213, ""))=0, 0, SUM(IF('Business Objects_Data'!F202:F213="03", 'Business Objects_Data'!P202:P213, ""))/E$4)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.