How can I eliminate #VALUE in calculated cells

[Solved/Closed]
Report
Posts
2
Registration date
Sunday December 2, 2012
Status
Member
Last seen
December 6, 2012
-
 hwrychen -
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
Hanswerner Rychen


10 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
have you looked into ISERROR function?
Posts
2368
Registration date
Sunday September 23, 2012
Status
Moderator
Last seen
December 13, 2018
40
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:

1. SUM (adds)
2. PRODUCT (multiplies)
3. QUOTIENT (divides)

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

=SUM(G10:H10)

Instead of:

=H10+G10

Please revert for clarification.
Posts
2
Registration date
Sunday December 2, 2012
Status
Member
Last seen
December 6, 2012

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 !
Hanswerner
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
you can post a sample book at some public file share site and post back the link to the file back here.
Posts
2368
Registration date
Sunday September 23, 2012
Status
Moderator
Last seen
December 13, 2018
40
Hi Hanswerner,

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.
I just made the upload of the Excel-File named "#Values! Elimination.xlsx"
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,
Hanswerner
Posts
2368
Registration date
Sunday September 23, 2012
Status
Moderator
Last seen
December 13, 2018
40
Hi Hanswerner,

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.

Anyway, here the required Links:

Download-Link: http://speedy.sh/XEEms/Values-Elimination.xlsx
Forum-Link: [code]http://speedy.sh/XEEms/Values-Elimination.xlsx/code
HTML_Link: <a href="http://speedy.sh/XEEms/Values-Elimination.xlsx">Download at SpeedyShare</a>

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

Many thanks for your great help.
Hanswerner
Posts
2368
Registration date
Sunday September 23, 2012
Status
Moderator
Last seen
December 13, 2018
40
Hi Hanswerner,

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

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

Do reply with results.
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,
Hanswerner

The Link for your download:

http://speedy.sh/HkmVV/More-Errors-Values-Elimination.xlsx
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Instead of
=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 !
Posts
2368
Registration date
Sunday September 23, 2012
Status
Moderator
Last seen
December 13, 2018
40
Hi Hanswerner,

Sorry for the delayed response, using the formula rizvisa1 has suggested I have tried and edited first part of your sheet (Anzahl Massen); hope you can edit the remaining sheet yourself. Here is the corrected sheet (ensure the results are correct before you print):

https://authentification.site/K3tFw/More-Errors-Values-Elimination-2.xlsx

Please revert for clarification.
Hi, Zohaib
thanks a lot for your suggestions.
My Worksheet is now ready and works fine.
Very greatful for all the formulas I didn't know, and will certainly help me in the future a lot resolving tricky things !
My best wishes for a happy Christmas and a prosperouse New Year.
Blessings from above, best health and much love all through 2013 !
Hanswerner

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!