# How can I eliminate #VALUE in calculated cells

Solved/Closed
hwrychen Posts 2 Registration date Sunday December 2, 2012 Status Member Last seen December 6, 2012 - Dec 2, 2012 at 02:21 PM
hwrychen - Dec 9, 2012 at 06:53 AM
Hello,

I am building an Excel calculation sheet for cookeries for christmas.

In cell H\$7 I have the input for how many times I would like to prepare "Bounties"
In column "D" I make the input of the material costs.
Column "G" has my inputs for the costs and quantity of one lot in money, kgs, spoons etc.
Column "H" has the following formula:
=IF((G10*H\$7)<>0;(G10*H\$7);"")
This formula works fine and eliminates all the zeros in the relatively big table.
Now, when I try to eliminate in column "I" all the appearing "#VALUE" indications with the following formula relating to empty cells of columns "G" and "H":
=IF((H10*\$D10)<>0;(H10*\$D10);"")
I don't find any reasonable way out and need help from a far more experienced Excel knower !!

Is any one able to help me to get a kleen table. Zeros and #VALUEindications are not of my taste !

Thanks for your help and best regards

## 10 replies

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Dec 2, 2012 at 07:53 PM
have you looked into ISERROR function?
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Dec 2, 2012 at 09:48 PM
Hi hwrychen,

If I understand you clearly, you are trying to obtain a value in column "I" using values in Values in Columns "G" and "H".

In Microsoft Excel, when you perform a mathematical operation on cells that contain text and values, you may receive a #VALUE! error. Although some functions correctly evaluate cells and ignore text strings, if you add the cells by using arithmetic operators such as addition (+), subtraction (-), multiplication (*), or division (/), an error value may occur. Instead of using an individual mathematical operator, use its equivalent worksheet function instead:

2. PRODUCT (multiplies)
3. QUOTIENT (divides)

For example if you are trying to add G10 and H10 use:

=SUM(G10:H10)

=H10+G10

hwrychen Posts 2 Registration date Sunday December 2, 2012 Status Member Last seen December 6, 2012
Dec 6, 2012 at 11:42 AM
Dear Zohaib,
thanks a lot for trying to help me. You did in a certain way missunderstand me. I know how to handle sum, products or quotient in Excel. This is not the problem.
I would have liked very much to send you the part of the Excel-sheet where you could have had a look at my question, but unfortunately there is no possibility existing to send an attachment to you.
So, in this case, we have to forget about all.
Many, many thanks for your help.
Best regards and all the best for a happy weekend !
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Dec 6, 2012 at 07:03 PM
you can post a sample book at some public file share site and post back the link to the file back here.
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Dec 6, 2012 at 07:24 PM

If possible please upload your sample file so that I can try to find a solution. You can use https://authentification.site to share your sample file.
Note: Please do not upload any file which has any personal or confidential data. If so, first remove the personal and confidential data and then upload.

Please do write back to us.

Thanks & Regards
Zohaib R
#iworkfordell
Dear Zohaib

thanks a lot for trying to help me.
You will see two tables, the left one with the#Values! and the one on the right hand side, as I would like to have the results.
Looking forwards to have your instructions, I remain, very grateful,

Didn't find the answer you are looking for?

Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Dec 6, 2012 at 09:21 PM

Thank you for writing to us, when you upload a file on https://authentification.site this website provides you with a URL (link) which you need to provide me so that I can download the file to my computer and then analyze.

Please do write back to us.
Dear Zohaib,

I am so sorry for all the trouble you have with me. I thought everybody has access to the uploaded file.

If there should still be a problem, please don't hesitate and contact me again.

Many thanks for your great help.
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Dec 7, 2012 at 05:38 AM

I have uploaded the file with corrected formula here, check if this helps:

https://authentification.site/GffkU/Copy-of-Values-Elimination.xlsx

Hi, Zohaib !
Partially it helped. Thanks for the tip.
I still have troubles with the two columns W and X !
Take Cells K13 and O3 and make any fictive input of 1.
You will see the result in columns W13 and X13

Go back now and erase one of the inputs in Cells K13 or O3 and you will see disappear the result in Cells W13 and X13.

Another problem I can't understand is the error in Cell W24 !

You don't need to hurry up with your response, I can wait, no problem.

Thanks again for your great instruction !
Wish you a wonderful and sunny weekend,

http://speedy.sh/HkmVV/More-Errors-Values-Elimination.xlsx
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Dec 7, 2012 at 06:49 PM
=IF(ISERROR(H13+L13+T13+T13),"",H13+L13+P13+T13)

you can use
=SUM(H13,L13,P13,T13)

and if you prefer not to see number when all cells are blank
then
=IF(AND(H13="",L13="",P13="",T13=""),"",SUM(H13,L13,P13,T13))

same is true for
=W11+W12+W13+W14+W15+W16+W17+W18
you can use
=SUM(W11,W12,W13,W14,W15,W16,W17,W18)
or
=SUM(W11:W18)

and if you want to see 0 in case all is "", then
=IF(AND(W11="",W12="",W13="",W14="",W15="",W16="",W17="",W18=""),"",SUM(W11:W18))
Hello rizvisa1 !
Just great, very great what you sent me !
Thanks for your extended help. Really very useful for me.
My case is in the meantime resolved:
Wish you a wonderful Christmas and all the best for the coming 2013. God bless you and keep you healthy !
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Dec 8, 2012 at 04:33 AM