![]() |
|
21 May 2014 | #11 |
|
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 Specs![]() |
. |
|
21 May 2014 | #12 |
|
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 Specs![]() |
21 May 2014 | #13 |
x64 (6.3.9600) Win8.1 Pro & soon dual boot x64 (6.1.7601) Win7_SP1 HomePrem
|
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 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 Specs![]() |
. |
|
22 May 2014 | #14 |
|
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 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 . Seriously though, Team effort. Good luck to you Sven. |
My System Specs![]() |
22 May 2014 | #15 |
|
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 |
My System Specs![]() |
22 May 2014 | #16 |
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? =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 Specs![]() |
23 May 2014 | #17 |
|
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 Specs![]() |
23 May 2014 | #18 |
|
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 |
My System Specs![]() |
23 May 2014 | #19 |
x64 (6.3.9600) Win8.1 Pro & soon dual boot x64 (6.1.7601) Win7_SP1 HomePrem
|
![]() 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 Specs![]() |
23 May 2014 | #20 |
|
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 Specs![]() |
![]() |
Thread Tools | |
Similar help and support threads | ||||
Thread | Forum | |||
Excel 2016: Displaying formulas in the formula bar I am using Win 7 Pro 7601 Multiprocessor Free SP1, 64-bit. Intel Core i7-4770, 16GB installed memory. Excel 2016. I have a spreadsheet with formulas, none of which display in the formula bar. All my other spreadsheets display their formulas in the bar. I have tried the following to display the... |
Microsoft Office | |||
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 | |||
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 | |||
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 14:27. |
![]() ![]() ![]() ![]() ![]() |