Forum Home
Press F1
Thread ID: 46651	2004-07-01 05:01:00	XL formula	B.M. (505)	Press F1

Post ID	Timestamp	Content	User
248831	2004-07-02 22:20:00	Bob - I'm pleased it worked out for you. Mike - Given almost all of us don't use anywhere near all of the functionality available in software such as Excel, it is probably better to only learn about the functions you will need. The best starting place (for me) is the online manual. Assuming you know some of the easy functions (such as +, -, /, *, IF, AND, OR, SUM, COUNT, MAX, MIN, ROUND etc.) then some of the fun functions are things like LEFT, RIGHT, MID, &, NOW, LEN, ISERR, SEARCH (search was a new one for me too!), FIND (same as search except it is case sensitive), TEXT, SUMIF, COUNTIF, TRIM, UPPER, LOWER, ABS, MOD, INT, PI, ^, RAND, SUMPRODUCT, TRUNC, AVERAGE, HLOOKUP, VLOOKUP, DATE, ISBLANK. These are just the tip of the iceberg insofar as Excel functions go but they would represent about 99% of the functions used by most users. In Excel help (turn off the annoying auto wizard thingy), go to the contents page, about half way down open (Im using XL2000) "Using Functions", open "Worksheet Reference Functions", and there are lists of functions for text, math, stats, lookup etc etc etc. This would be your best starting place. Andrew	andrew93 (249)
248832	2004-07-02 23:52:00	> In Excel help (turn off the annoying auto wizard thingy) Thanks very much for that tip, Andrew. :-) I never knew it would make such a difference to how much more I could learn by using Excel's online help with the damned thing out of the way. That cursed wizard is more of a hindrance than a help and I never realised it until now. :|	Susan B (19)
248833	2004-07-03 00:39:00	> This from an amatuer xl user. I use xl a lot for > doing pricelists etc. Where is a good place to go to > learn the basics of writing formulas? I know the > basics but would love to learn more. > > cheers > Mike Hi Mike, I suggest downloadingPeter Noneley's Excel Function Dictionary (homepage.ntlworld.com) which lists functions in Excel and gives an example of each. Quite well done and a nice compliment to Excel help. Theres a lot of resources on the net but heres some good resources.... www.mrexcel.com www.cpearson.com j-walk.com http://www.tushar-mehta.com/ http://peltiertech.com/ Mskb (support.microsoft.com)	parry (27)
248834	2004-07-05 22:47:00	Ok, guys all is working well but I wonder if the formula can be adapted to include “paper” as well as “paint”. I’ve had a go but no luck. What we’re looking to do is search the same cell for “paint” OR “paper and return the same values if either are found. Cheers Bob	B.M. (505)
248835	2004-07-05 22:50:00	PS I suppose I should have put we're working on RussellD's formula at the moment: =IF(A6="","",IF(OR(E6="",AT6="",BB6="X"),0,IF(ISERR(SEARCH("paint",AV6)), (AT6-E6)*$CS$5,0)))	B.M. (505)
248836	2004-07-08 13:26:00	> Ok, guys all is working well but I wonder if the formula can be adapted > to include “paper” as well as “paint”. > > =IF(A6="","",IF(OR(E6="",AT6="",BB6="X"),0,IF(ISERR(SEA > RCH("paint",AV6)), (AT6-E6)*$CS$5,0))) (note : I abridged the previous posts for brevity) Hi Bob This got me thinking ..... hmmm, tricky, what to do, what to do ....? The "search" and "iserr" functions don't naturally lend themselves to the "OR" function but given this is a completely backwards or unnatural test then quite (un)naturally we use the AND function instead. Swap this part of your formula : IF(ISERR(SEARCH("paint",AV6)) for this : IF(AND(ISERR(SEARCH("paint",AV6)),ISERR(SEARCH("paper",AV6))) so that your formula looks like this : =IF(A6="","",IF(OR(E6="",AT6="",BB6="X"),0, IF(AND(ISERR(SEARCH("paint",AV6)),ISERR(SEARCH("paper",AV6))), (AT6-E6)*$CS$5,0))) Note : I have inserted carriage returns to ensure the formula is not buggered up by pressf1 formatting so hopefully it still makes sense. What this is doing it testing for an error with paint and paper, if both tests for paper and paint return an error, then neither word was present, the ISERR condition is tripped, and the (AT6-E6)*$CS$5 bit is done. If either paper or paint (or both) were present, then the ISERR is not tripped and the formula returns the value 0. (As per Russell D's previous post, if the intended outcome is backwards, then you need to swap over the 0 and (AT6-E6)*$CS$5 bits) This formula is getting big and ugly (a bit like Shrek) and I'm starting to wonder if we are making 3 left turns to make 1 right turn - but I'll leave that up to you. BTW, if you don't mind me asking, what's the context of this formula? Cheers, Andrew.	andrew93 (249)
248837	2004-07-08 13:38:00	There often does come a time when trying to make up more and more complex Excel formulae just isn't really worth the trouble. A bit of code writing in Visual Basic or whatever really really does make life and time so much easier and more rewarding. That, after all, was the main reason that Blaise Pascal, Charles Babbage, Alan Turing et al invented computers in the first place, so these machines can do the donkey work according to specified instructions (programs). If you spend your time making formulae more and more complex, no prizes for guessing who's doing the donkey work. :-(	rugila (214)
248838	2004-07-09 06:07:00	> There often does come a time when trying to make up > more and more complex Excel formulae just isn't > really worth the trouble. > > A bit of code writing in Visual Basic or whatever > really really does make life and time so much easier > and more rewarding. (shortened) > If you spend your time making formulae more and more > complex, no prizes for guessing who's doing the > donkey work. I agree that cutting code to solve a problem is rewarding but I wouldn't go so far as to say that this is an overly complex formula - it is messy and tricky, but it's not complex. Insofar as the "donkey work" and "worth the trouble" goes, the same conditions would have to be tested in VB to come up with the same result. Given computers are stupid and will only do what you tell them (or programme them) to do, then the user still has to input the variables and formula anyway. I suspect that the code in VB would consist of a number of nested if statements as well as a variable declaration section (correct me if I am wrong) resulting in a longer formula overall. The VB formulae may appear simpler because they are spread over a number of lines, but then that's the beauty of XL as a tool for non-programmers. So would VB be more efficient? Who knows .... but I doubt it, because Bob would have used VB or Access if the original task lent itself to that software. As I stated earlier in an earlier post, we may be making 3 left turns to execute a right turn and IMHO this formula could be tidied up, but without knowing the context of the formula and the particular problem it is trying to solve, then I think it is a little premature to suggest a better tool when Bob was looking for an answer to a specific XL problem. ...but that's just my opinion. :)	andrew93 (249)
248839	2004-07-14 03:17:00	Sorry I’m so slow getting back Andrew. Yes, I found the “Search” function did not fit particularly well with the “OR” function. In the meantime I went back to the “Right” function and found it fitted in with the “OR” function no problem. It would appear the “Search” function is not as versatile as many others and likes to be restricted to one or two search’s only. Anyway, the problem is solved and the discussion has interested others, which is a bonus. :) As for your formula being ugly Andrew, let me assure you it was a thing of great beauty compared to some of mine! :D Yes rugila it would be nice to be able to write programmes, but I doubt there’s a calculation that XL can’t already perform. It’s rather a matter of knowing what is available and then getting the comma, parentheses, whatever, in the right place. :( Oh, I’ll start another thread, but, do you guys play at all with neural’s or neural engines?	B.M. (505)
248840	2004-07-14 03:48:00	Looking at this quickly again, try changing "SEARCH" to "FIND" and "paint" to "pa*" in the formula. "pa*" will find both "paint" and "paper". HTH	Russell D (18)
1 2 3