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
hwrychen - Dec 9, 2012 at 06:53 AM
Related:
- How can I eliminate #VALUE in calculated cells
- How to calculate position in excel ✓ - Excel Forum
- Excel macro to create new sheet based on value in cells - Guide
- If a cell has text then return value ✓ - Excel Forum
- If cell contains date then return value ✓ - Office Software Forum
- How would you change all cells containing the word pass to green - Excel Forum
10 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Dec 2, 2012 at 07:53 PM
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
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:
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.
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.
hwrychen
Posts
2
Registration date
Sunday December 2, 2012
Status
Member
Last seen
December 6, 2012
Dec 6, 2012 at 11:42 AM
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 !
Hanswerner
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Dec 6, 2012 at 07:03 PM
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
Dec 6, 2012 at 07:24 PM
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
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
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
Didn't find the answer you are looking for?
Ask a question
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
Dec 6, 2012 at 09:21 PM
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.
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
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
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
Dec 7, 2012 at 05:38 AM
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.
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
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Dec 7, 2012 at 06:49 PM
Dec 7, 2012 at 06:49 PM
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))
=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))
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
Dec 8, 2012 at 04:33 AM
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.
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
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
