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:
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":
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
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:
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.
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,
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.
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,
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):
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 !