Windows 7 Forums

Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Hide formulas

18 May 2014   #1
Sven1458

windows 7 ultimate x64
 
 
Hide formulas

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




Attached Thumbnails
Hide formulas-capture.jpg  
My System SpecsSystem Spec
.
18 May 2014   #2
ignatzatsonic

Microsoft Community Contributor Award Recipient

Windows 7 Home Premium SP1, 64-bit
 
 

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.
My System SpecsSystem Spec
18 May 2014   #3
Sven1458

windows 7 ultimate x64
 
 

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
My System SpecsSystem Spec
.

18 May 2014   #4
Slartybart

x64 (6.3.9600) Win8.1 Pro & soon dual boot x64 (6.1.7601) Win7_SP1 HomePrem
 
 

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
.
My System SpecsSystem Spec
18 May 2014   #5
Sven1458

windows 7 ultimate x64
 
 

Thanks Slartybart,
I will try to figure it out. I never wrote anything like that before, just regular formulas.
Thanks again
Sven
My System SpecsSystem Spec
19 May 2014   #6
Sven1458

windows 7 ultimate x64
 
 

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
My System SpecsSystem Spec
20 May 2014   #7
TanyaC

Linux Mint 17 Cinnamon | Win 7 Ult x64
 
 

Quote   Quote: Originally Posted by Slartybart View Post
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
My System SpecsSystem Spec
20 May 2014   #8
Slartybart

x64 (6.3.9600) Win8.1 Pro & soon dual boot x64 (6.1.7601) Win7_SP1 HomePrem
 
 

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


Attached Images
Hide formulas-capture.png 
My System SpecsSystem Spec
21 May 2014   #9
RickL1

Windows 7 Home 32bit
 
 

Quote   Quote: Originally Posted by Sven1458 View Post
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
My System SpecsSystem Spec
21 May 2014   #10
Sven1458

windows 7 ultimate x64
 
 

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
My System SpecsSystem Spec
Reply

 Hide formulas




Thread Tools Search this Thread
Search this Thread:

Advanced Search




Similar help and support threads
Thread Forum
Hide program hide popup.
I have windows media recorder set to record local talk radio for most of the day I have it set to record by the hour, it pops up to alert you its recording....every hour on the hour...and it pops up above EVERYTHING its driving me crazy, anyone know of a hide program program that can hide new...
Software
Taskbar (Auto-Hide) doesn't hide all the way?
My friend was wandering how you could get rid of that barely top part of the task bar when it is in auto-hide. So is this possible? Note: You have to look really close to see it.
Themes and Styles
Auto-hide Taskbar Fails to Auto-hide
I set the Taskbar tab (in Taskbar & Start menu Properties) to Auto-hide the Taskbar. When I select Apply, the taskbar dips and disappears for a second and then comes right back and stays there. Is there a way to make the auto-hide work? W7 Home 6.1 / 7600 Thank you
General Discussion
A Program For Mathematical Drawings and Formulas
Hi, Does anybody know a program, using which I will be able to make mathematical drawings and write formulas at the same time? Any help will be greatly appreciated!
Software
how do you hide ur ip ??
i was barred / band from a site :) nothing serious had a dissagreement , anyway they new my ip so i gather this is how i was banned . as when i go to enter i cant. so i downloaded hide my ip full version , got back on the site using a fake ip address in england . anyway used that same ip...
General Discussion
Make explorer hide folders or hide files?
In Win 7 64 bit explorer, can I make it show me only folders or only files? Another way of saying this: can I make it hide folders, or hide files?
General Discussion


Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 03:58.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App