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

21 May 2014   #11
TanyaC

Linux Mint 17 Cinnamon | Win 7 Ult x64
 
 

If you are going to insist on using negative logic the correct formula is..

if($A1<>"",do calculations,"")

This is copied into each cell where you want to do calculations.

Using the $ in front of the A mans that column A will be used as the test value for all formulas.

Tanya


My System SpecsSystem Spec
.
21 May 2014   #12
Sven1458

windows 7 ultimate x64
 
 

I GOT IT!!
I finally got it going, thanks to all who helped
took me a while, but I also learned something new.
Thanks Again

Sven
My System SpecsSystem Spec
21 May 2014   #13
Slartybart

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

Quote   Quote: Originally Posted by TanyaC View Post
If you are going to insist on using negative logic the correct formula is..

if($A1<>"",do calculations,"")

This is copied into each cell where you want to do calculations.

Using the $ in front of the A mans that column A will be used as the test value for all formulas.

Tanya
Thanks Tanya - I insist on very little.
Not negative logic, just logic (that wasn't a double negative there was it?)

I'm just having a bit of fun with you, now that Sven is on his way.
Side note: I mentioned Band for a million years to a friend who is in a band and he's considering changing the name.

Please credit Tanya (press the scale icon on her post) for giving you the correct syntax.

Good job Sven, glad you're on the way to becoming the company Excel guru.

Bill
.
My System SpecsSystem Spec
.

22 May 2014   #14
TanyaC

Linux Mint 17 Cinnamon | Win 7 Ult x64
 
 

Quote   Quote: Originally Posted by Slartybart View Post
Quote   Quote: Originally Posted by TanyaC View Post
If you are going to insist on using negative logic the correct formula is..

if($A1<>"",do calculations,"")

This is copied into each cell where you want to do calculations.

Using the $ in front of the A mans that column A will be used as the test value for all formulas.

Tanya
Thanks Tanya - I insist on very little.
Not negative logic, just logic (that wasn't a double negative there was it?)

I'm just having a bit of fun with you, now that Sven is on his way.
Side note: I mentioned Band for a million years to a friend who is in a band and he's considering changing the name.

Please credit Tanya (press the scale icon on her post) for giving you the correct syntax.

Good job Sven, glad you're on the way to becoming the company Excel guru.

Bill
.
Hehe.. I used to get told off all the time when I did my first excel course at school. The teacher was paranoid about using positive logic... I guess it just stuck.

Seriously though, Team effort.

Good luck to you Sven.
My System SpecsSystem Spec
22 May 2014   #15
Sven1458

windows 7 ultimate x64
 
 

Hi All,
Thanks to SlartybartandTanyaC I have my spreadsheet working and I was wondering if there was a way to further improve on it, here is what I would like to do:

In the attachment you see 4 columns:Total Height, Total Width, Operation Side and Operation System.
In the Column Operation System you can see 3 types,
STR = STRAP
CRA= CRANK
MOT= MOTOR
Then there is the column: Axle Type and the options are 40 or 60.
40 only is used if a strap is used, 60 is for Crank or motor. When I type STR in the field, I would like 40 to appear in the column AXLE Type. When I type CRA or MOT I would like 60 to appear instead. Is that doable?
Furthermore, when a 40 appears, the deduction from total width is 6.5 and the total is shown in the column Axle width.
If 60 appears the deduction is 10. The formula has to be adjusted. Doable?

Thanks
Sven



Attached Thumbnails
Hide formulas-capture.png  
My System SpecsSystem Spec
22 May 2014   #16
Slartybart

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

Sure, more conditional testing

The easiest way to learn is open the function wizard. type an equal sign into the cell you want your result. You should see an fx button on your toolbar (near the sum 'E' icon) - select logical from the dropdown box and select if
That should give you fields to fill (test, then, else).

Play with it and have fun doing something new.

in the axle column (K) write a formula testing the operation system column(E) value
=if(E3 == "STR";40;if(E3== ("CRA" OR "MOT"); 60;"ERROR"))

I'm sure the syntax is all wrong, I pick row number 3 at random and the column letters are off too. But it is just an example.

If you start in the function wizard, you can see the results immediately.

The function is a bit trickier.
if E3 = STR write 40
otherwise test to see if E3 = CRA OR MOT - write 60 if that test passes
otherwise write an error msg - you have to decide what to do if none of the acceptable values are typed in the op system column. (My msg is really too long - maybe write a noticeable numeric - say 99)

Our friend Tanya might stop back and give you the correct syntax, but give it a shot. You'll be surprised how easy it is once you understand a few basics. Take a look at what Tanya posted and the function you already wrote - I'm sure it will come together for you.

Quote:
Furthermore, when a 40 appears, the deduction from total width is 6.5 and the total is shown in the column Axle width.
If 60 appears the deduction is 10. The formula has to be adjusted. Doable?
yes doable. similar function in the total width column
=if(e3 = "STR"; a3-6.5;if(E3== ("CRA" OR "MOT"); a3-10; ERROR))

Man am I rusty at this stuff - imagine that I used to write excel functions for accounting in my spare time. You think they'd be good at math wouldn't you


=If (not; "post a call for help"; "we're here")
My System SpecsSystem Spec
23 May 2014   #17
Sven1458

windows 7 ultimate x64
 
 

outstanding advice.
thank you very much. after I got the "old stuff" done, I feel like trying new stuff and this is what I want to try.
thanks for pointing me in the right direction. I'll be an Excel wizard in no time.
I check back to see if Tanya has any advice. This will keep me out of trouble over the long weeend.
Thanks again
Sven
My System SpecsSystem Spec
23 May 2014   #18
TanyaC

Linux Mint 17 Cinnamon | Win 7 Ult x64
 
 

Here you go...

I had to zip the xlsx file because I couldn't upload a spreadsheet file, so unzip it. It's called sven.xlsx.

Have a look, and ask any questions you have..

Tanya


Attached Files
File Type: rar sven.rar (8.2 KB, 3 views)
My System SpecsSystem Spec
23 May 2014   #19
Slartybart

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

Quote   Quote: Originally Posted by TanyaC View Post
Here you go...

I had to zip the xlsx file because I couldn't upload a spreadsheet file, so unzip it. It's called sven.xlsx.

Have a look, and ask any questions you have..

Tanya
You are too good, it is much easier to understand a formula if you can see a real one. You also caught what I missed in the column calcs (opps wrong column - that's what I get for doing this ad hoc... not in excel or even calc - just winging it

Thanks Tanya.


@Sven: I do have one small mod to the axel formulas - people can type anything in the cells, not only the 3 accepted strings. After playing with your spreadsheet, this input validation could be on all cells (that changes the entire project - test cell input, flag or disallow erroneous input, and the test for null cells would have to change). Maybe Sven can build a better mousetrap after putting this "easy" one to bed.

Axel Type Formula: =IF($F5="","",IF($F5="STR",40,60))
I would add a tertiary test to check for CRA or MOT
=IF($F5="","",IF($F5="STR",40,IF(($F5="CRA") OR ($F5="MOT"),60,"ERR")))

Hope I "converted" Calc to Excel correctly - might have missed a ($) or (;)

You can get quite detailed in your formulae, validating user input. Play around with what you have now - type in strange values, only fill in some cells - see what happens and determine how much you want to invest in this skunkworks project of yours.

I think you have enough confidence writing excel formulas now. Lean on the wizard for a while - it still helps me resolve errors.

Google search for Excel formulas. They might help or they might distract. Help in Excel is fairly well written and there are examples you can steal (I think - there used to be anyway)

Bill
.
My System SpecsSystem Spec
23 May 2014   #20
TanyaC

Linux Mint 17 Cinnamon | Win 7 Ult x64
 
 

Hi,

This is not a valid excel formula...

=IF($F5="","",IF($F5="STR",40,IF(($F5="CRA") OR ($F5="MOT"),60,"ERR")))

This is the corrected formula...

=IF($F5="","",IF($F5="STR",40,IF(OR($F5="CRA",$F5="MOT"),60,"ERR")))

You were close...

Tanya
My System SpecsSystem Spec
Reply

 Hide formulas




Thread Tools




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 00:28.
Twitter Facebook Google+ Seven Forums iOS App Seven Forums Android App