Help need someone to "translate" formulae

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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. ;)
 
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. :)
|
| 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. ;)
|
|
| --
| Faz
| ------------------------------------------------------------------------
| Faz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27830
| View this thread: http://www.excelforum.com/showthread.php?threadid=473723
|
 
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)
 
| 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
 
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.
 
Back
Top