Hide formulas

Sven1458 · 18 May 2014

Hi All,
I am designing a form in Excel to be used in manufacturing.
Since math seems to be a problem in our company I included some formulas to automate the process and cut down on mistakes.
In the screen shot you can see the first 4 columns that have to be entered by the user and then the form will calculate the fields:
Slat width
Rail Height
Housing width
Axle width
If there is nothing entered in the first 4 columns, I would like the fields that do calculations to be blank to make it less confusing. They should only show anything when a value is entered in column 1-4
So the question is how do I do that?
Anyone?
Thanks
Sven

ignatzatsonic · 18 May 2014

There's a setting in Excel Options/advanced that has a checkbox for "show a zero in cells that have a zero value".

The default is to have it checked, which will show 0.

Try unchecking it.

Sven1458 · 18 May 2014

Thanks for that tip, it works on the field that has a "0" in it, but not on the other fields because they show a "6.5"
or "0.8". Is there a way to make those disappear?

Sven

Slartybart · 18 May 2014

make your formula conditional in each cell you want supressed

If (A1 != ""; calculate value; "")

If cell A1 is NOT null
calculate the number

else
write null

could be blank or null string.

It's been a long time since I wrote Excel formula's - sorry it isn't exact

Hopefully you can hit the formula help and work out the exact syntax

Bill
.

Sven1458 · 18 May 2014

Thanks Slartybart,
I will try to figure it out. I never wrote anything like that before, just regular formulas.
Thanks again
Sven

Sven1458 · 19 May 2014

Slartybart,
I tried to get this going, but can not make it work.
can you give me the exact formula, looking at line 1 in my screen shot?
total width = 100 should go to 93.5 in slat width and have nothing show when there is on entry in total width.
thanks for your help
Sven

TanyaC · 20 May 2014

Slartybart said:

make your formula conditional in each cell you want supressed

If (A1 != ""; calculate value; "")

Pardon me for jumping in. Just to elucidate on Bill's formula,

In the each of the cells that performs a calculation you would test the value of the dependent cell first, as Bill has shown.

So, If we call the first column A, then the formula in EACH of the calculated cells is..

Using positive logic: IF($A1="","",do calculations)

or

Using Negative Logic: IF($A1<>"",do calculations,"")

The operators for "Not equal" are <>, not !=

I was always taught to use positive logic. But either will work.

Now, as regards the Dollar sign; This allows you to copy the formula across other columns and it will always check column A's value for NULL. By not using a $ before the 1, when we copy down we will get the correct row reference.

Tanya

Slartybart · 20 May 2014

this should help.... it's in OpenOffice Calc, but spreadsheet formulas are somewhat similar if not the same

=IF(B2;93.5;"")

edit: Ignore my typo in the image - B2 is correct if I understand what you want - B3 would test the height not the width.... sorry 'bout that.

basically says if there's a value, any value in column 2, set the current cell to 93.5, otherwise make it blank

Try that simple formula first, then add your own calculation where I put a static 93.5

RickL1 · 21 May 2014

Sven1458 said:

I am designing a form in Excel to be used in manufacturing.
Since math seems to be a problem in our company I included some formulas to automate the process and cut down on mistakes.
.......
They should only show anything when a value is entered in column 1-4
So the question is how do I do that?

There are several ways to achieve your goal, and I applaud your efforts to make improvements at your workplace.
I will take the approach of not showing the formula result until multiple cells contain the source values entered by the user. If you need to have all 4 entries before doing the calculation then this could work
=IF(COUNT(A2:2)=4,SUM(A2:2),"")
which says to count the cells containing a number in the range a2-d2 and if that equals 4 then do the calculation sum of the range a2-d2 . In my version of Excel the Count function only counts a cell if the value is a number.
If you want to be able to count a cell that contains either letters or numbers then use
=IF(COUNTA(A2:2)=4,SUM(A2:2),"")
but be aware that this will count a cell that contains a space character (looks empty but it is not).

Rick

Sven1458 · 21 May 2014

Thanks to all who responded.
I think I am misunderstanding what you are telling me.
The formulas are in the sheet, where do I put: If (A1 != ""; calculate value; "")
I am ruing office 2013.

I tried to put this with the formula, but that must be wrong.
Thanks