Windows 7: Hide formulas

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

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

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.

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

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.

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")

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

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
.

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