 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2007Help need someone to "translate" formulae
Hi, I have downloaded a timesheet template from Microsoft downloads. I would really like to understand how formulae work but I am a complete beginner. I wonder if some kind soul would "translate" the following formulae for me just to give me an idea of what's going on (and I do mean complete beginner please don't assume I know anything, step by little itsy bitsy step please!) =IF((OR(B12="",B11="")),0,IF((B12<B11),((B12-B11)*24)+24,(B12-B11)*24)) Where B11 = start time and B12 = finish time =IF(OR(ISTEXT(C12),ISTEXT(C15)),"Error in C12 or C15",(C12+C15)) C12 = answer for above = total hours am C15 = total hours pm Many thanks  SignatureMifty Hi Mifty, that's a nice name. :) Basically, formulae or equations are just commands for the computer, so that it knows exactly what calculations to make. I consider it to be translating English to the language that the spreadsheet knows. This first equation is an example of a nested IF statement. IF statements are usually presented like this =IF(A1=8,"Yes","No") This example is telling the computer, if cell A1 is equal to 8, then this cell will be "Yes", otherwise "No". Nested IF statements are IF statements within IF statements, like this =IF(A1>80%,"A",IF(A1>70%,"B",IF(A1>60%,"C",IF(A1>50%,"D",IF(A1>40%,"E","U"))))) (In English!) This would mean if the value is above 80% it's an A, otherwise if it's not above 80%, but it's above 70% it's a B, etc. So this would be the percentage a student got on their exam and their grade. It's always important that the total number of open brackets is the same as closed brackets in a formula. If you know how to use brackets effectively, you shouldn't need to worry. =IF(A1=1,IF(A2=1,IF(A3=1,"Match",""),""),"") This is another example, the formula checks if all 3 cells are equal to 1, if they are it's a "Match" otherwise the cell is left empty. Notice how the closed bracket is put in place to show that the function ends. All functions within a formula start with ( and end with ) this is crucial to remember if you deal with functions inside other functions, like this one. =IF(A1=70,LEFT(A2,4),RIGHT(A2,1)) There's 2 closed brackets at the end of this equation, the first marks the end of the RIGHT function, the second is the end of the IF statement, starting the formula. For a detailed run through of every function in excel, select an empty cell, go to Insert at the top, and select Function, or Insert Function. This should help you understand a little more about equations and functions, i'll let you try to work out those you posted, if you're still struggling, i'll post some more tips. ;) SignatureFaz G'day Faz, (that's an interesting name) thanks for the time and energy spent in the explanation, I have saved it to word proc doc. Any hints on where to begin, when attempting to reverse engineer a formula? here is one I use in MSWorks as an example: =IF(ISERROR(SMALL(IF($B$1:$B$64448=B64453,ROW($E$1:$E$64448)),$F$1517)),0,INDEX($E$1:$E64448,SMALL(IF($B$1:$B$64448=B644 53,ROW($E$1:$E$64448)),$F$1517))) Where does one start? I presume there must be an accepted order the computer must read not necessarily left to right. | Hi Mifty, that's a nice name. :) | [quoted text clipped - 44 lines] | Faz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27830 | View this thread: http://www.excelforum.com/showthread.php?threadid=473723 Thank you, thank you ! SignatureMifty > Hi Mifty, that's a nice name. :) > [quoted text clipped - 38 lines] > functions, i'll let you try to work out those you posted, if you're > still struggling, i'll post some more tips. ;) Ping! I think I've got it. If cells B12 or B11 are empty then 0 is entered into the cell, If B12 is less than B11 carry out the calculation but add 24 otherwise just do calc 2nd one enter error..... if there is text in either of the cells otherwise do calc. Pathetic being so excited over such a small thing. Many thanks for your help Faz. But as Rodney says how do you work out the correct syntax and also do you know of any sites that explain the functions in a very simple way. I tried your suggestion of looking at the function in the worksheet but if you have absolutely no idea (like me) what they do or in what context they are used that won't help with many of them. Perhaps it's my maths and logic that are at fault here! Thanks again Mifty (still smiling) SignatureMifty > Hi Mifty, that's a nice name. :) > [quoted text clipped - 38 lines] > functions, i'll let you try to work out those you posted, if you're > still struggling, i'll post some more tips. ;) | Pathetic being so excited over such a small thing. I share your enthusiasm :) being able to quickly write suitable formula is a real buzz. I am genearlly using MSWorks, but dipping the occaisional toe in Excel, and its becoming a preference due to the ability to have it work as a flat file database with 65,000 rows. A magnificent easy publiication for formula and its explanation is the marvelous handbook MSWorks Version 3.00 but it is now darn hard to locate. Good Luck Cheers Rodney! SignatureMifty > | Pathetic being so excited over such a small thing. > [quoted text clipped - 9 lines] > > Good Luck A good way to find out how a formular breaks down is in excel to just click on the insert function symbol (fx) this will open up a formular window where you can select what formular you wish to use select the one you want and under that it will tell you what is required for it to work and yo can also click on the link help on this function for more information. > Cheers Rodney! > [quoted text clipped - 11 lines] > > > > Good Luck
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.