Forum home Road cycling forum The cake stop

Excel Online Training

13»

Posts

  • ProssPross Posts: 28,322
    What have I started???!! :shock:
  • chris_basschris_bass Posts: 4,913
    Pross wrote:
    What have I started???!! :shock:

    my heaven :D
    www.conjunctivitis.com - a site for sore eyes
  • TheBigBeanTheBigBean Posts: 13,216
    Chris Bass wrote:
    TheBigBean wrote:
    Chris Bass wrote:
    TheBigBean wrote:
    Slowbike wrote:
    TheBigBean wrote:
    Geeky Excel aside - I came across someone who, due to the lack of a PRODUCTIF funtion, had taken logs of everything and used SUMIF.

    I still use "=if(iserror(something),anotherformula,whatever)" despite being shown (by my wife) the iferror formula ... think how many more nested codes I could get in if I shortened it just a little ;)

    Sigh. IFERROR(thing to do, thing to do if an error)

    FTFY

    No, you broke it.

    how so?

    You are excluding every possible error not the reason that you have an error. Eg. IF(B=0,0,A/B). With your formula it would result in 0 even if B was non zero, but A was an error feeding from elsewhere. Essentially, it's lazy and poor practice, and widely used.

    This brings us back vagiely on topic. It is relatively straightforward to learn best practice software coding, but Excel seems to mostly be a free for all, so it would be nice if there was some basic training available.
  • tailwindhometailwindhome Posts: 16,486
    Chris Bass wrote:
    keef66 wrote:
    I'd have thought if accounting was involved she'd already have been exposed to Excel.

    All our accountants fcuking love spreadsheets!

    hang on, hang on..... there are people that don't like spreadsheets?

    The world divides into people who use Word and people who use Excel
    Believe that a farther shore
    Is reachable from here.
    Believe in miracles
    And cures and healing wells
  • chris_basschris_bass Posts: 4,913
    TheBigBean wrote:
    Chris Bass wrote:
    TheBigBean wrote:
    Chris Bass wrote:
    TheBigBean wrote:
    Slowbike wrote:
    TheBigBean wrote:
    Geeky Excel aside - I came across someone who, due to the lack of a PRODUCTIF funtion, had taken logs of everything and used SUMIF.

    I still use "=if(iserror(something),anotherformula,whatever)" despite being shown (by my wife) the iferror formula ... think how many more nested codes I could get in if I shortened it just a little ;)

    Sigh. IFERROR(thing to do, thing to do if an error)

    FTFY

    No, you broke it.

    how so?

    You are excluding every possible error not the reason that you have an error. Eg. IF(B=0,0,A/B). With your formula it would result in 0 even if B was non zero, but A was an error feeding from elsewhere. Essentially, it's lazy and poor practice, and widely used.

    This brings us back vagiely on topic. It is relatively straightforward to learn best practice software coding, but Excel seems to mostly be a free for all, so it would be nice if there was some basic training available.

    what?! that isn't what iferror does (i was going back to what slowbike said):

    Syntax
    IFERROR(value, value_if_error)

    The IFERROR function syntax has the following arguments:

    value Required. The argument that is checked for an error.

    value_if_error Required. The value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.
    www.conjunctivitis.com - a site for sore eyes
  • morstarmorstar Posts: 4,470
    Back to the OP. The first reply is the best answer.
    I am a pretty advanced Excel user with formulas and data modelling and yet failed a basic user test once which was heavily focussed on keyboard shortcuts and navigation skills.
    I have subsequently got better at such things but they are nice to haves rather than Excel skills imho.
    Be able to navigate around a spreadsheet, sum a range of data and create / rename some tabs would be the sort of basic skills I'd look at.
  • chris_basschris_bass Posts: 4,913
    yeah i agree (i wasn't suggesting they should learn the stuff above!)

    I think useful functions to know would be sum, average, vlookup/hlookup (knowing that ,0 at the end means it doesn't need to be in order is a good thing to know)

    good to have would be knowing the difference between count and counta (counta counts numeric and text, count just counts numeric cells)

    then things like formatting cells, knowing F4 cycles through locking cells, how to paste special (like transpose and values)

    then stuff like making a pivot table and changing what it displays nothing too complicated though.
    www.conjunctivitis.com - a site for sore eyes
  • TheBigBeanTheBigBean Posts: 13,216
    Chris Bass wrote:
    TheBigBean wrote:
    You are excluding every possible error not the reason that you have an error. Eg. IF(B=0,0,A/B). With your formula it would result in 0 even if B was non zero, but A was an error feeding from elsewhere. Essentially, it's lazy and poor practice, and widely used.

    This brings us back vagiely on topic. It is relatively straightforward to learn best practice software coding, but Excel seems to mostly be a free for all, so it would be nice if there was some basic training available.

    what?! that isn't what iferror does (i was going back to what slowbike said):

    Syntax
    IFERROR(value, value_if_error)

    The IFERROR function syntax has the following arguments:

    value Required. The argument that is checked for an error.

    value_if_error Required. The value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

    I don't know how to write it any clearer. IFERROR excludes all errors (see emphasis above) not the reason for the error e.g. you might want to divide by zero. Restating my formula above as IFERROR(A/B,0) results in zero if A is a #VALUE! error (or anything else). That's not very helpful and not the original reason for including it.
  • TheBigBeanTheBigBean Posts: 13,216
    Chris Bass wrote:
    keef66 wrote:
    I'd have thought if accounting was involved she'd already have been exposed to Excel.

    All our accountants fcuking love spreadsheets!

    hang on, hang on..... there are people that don't like spreadsheets?

    The world divides into people who use Word and people who use Excel

    What a blissful life you must have led to have not come across the Powerpoint people.
  • rjsterryrjsterry Posts: 20,167
    TheBigBean wrote:
    Chris Bass wrote:
    keef66 wrote:
    I'd have thought if accounting was involved she'd already have been exposed to Excel.

    All our accountants fcuking love spreadsheets!

    hang on, hang on..... there are people that don't like spreadsheets?

    The world divides into people who use Word and people who use Excel

    What a blissful life you must have led to have not come across the Powerpoint people.

    One for the intriguing thread is how such a dog of a piece of software like Word became so ubiquitous.
    1985 Mercian King of Mercia - work in progress (Hah! Who am I kidding?)
    Pinnacle Monzonite

    Liberal metropolitan, remoaner, traitor, "sympathiser", etc.
  • TheBigBeanTheBigBean Posts: 13,216
    Word is fine*. Powerpoint is the enemy.

    *except it needs to allow infinite paper size, thus ending the use of Excel for tables full of text discussion.
  • rjsterryrjsterry Posts: 20,167
    TheBigBean wrote:
    Word is fine*. Powerpoint is the enemy.

    *except it needs to allow infinite paper size, thus ending the use of Excel for tables full of text discussion.

    If it were fine it wouldn't make random unrequested changes to formatting, font and type size and would include a workable tables function that integrated properly with Excel.

    I agree that PowerPoint is Word's delinquent cousin.
    1985 Mercian King of Mercia - work in progress (Hah! Who am I kidding?)
    Pinnacle Monzonite

    Liberal metropolitan, remoaner, traitor, "sympathiser", etc.
  • rjsterry wrote:
    TheBigBean wrote:
    Word is fine*. Powerpoint is the enemy.

    *except it needs to allow infinite paper size, thus ending the use of Excel for tables full of text discussion.

    If it were fine it wouldn't make random unrequested changes to formatting, font and type size and would include a workable tables function that integrated properly with Excel.

    I agree that PowerPoint is Word's delinquent cousin.
    My students are forbidden to use it. They have to make video instead, which they must appear in. Half they have to narrate live, rest narrate in the video.
  • rjsterryrjsterry Posts: 20,167
    rjsterry wrote:
    TheBigBean wrote:
    Word is fine*. Powerpoint is the enemy.

    *except it needs to allow infinite paper size, thus ending the use of Excel for tables full of text discussion.

    If it were fine it wouldn't make random unrequested changes to formatting, font and type size and would include a workable tables function that integrated properly with Excel.

    I agree that PowerPoint is Word's delinquent cousin.
    My students are forbidden to use it. They have to make video instead, which they must appear in. Half they have to narrate live, rest narrate in the video.
    What do you teach?
    1985 Mercian King of Mercia - work in progress (Hah! Who am I kidding?)
    Pinnacle Monzonite

    Liberal metropolitan, remoaner, traitor, "sympathiser", etc.
  • briantrumpetbriantrumpet Posts: 7,035
    rjsterry wrote:
    rjsterry wrote:
    TheBigBean wrote:
    Word is fine*. Powerpoint is the enemy.

    *except it needs to allow infinite paper size, thus ending the use of Excel for tables full of text discussion.

    If it were fine it wouldn't make random unrequested changes to formatting, font and type size and would include a workable tables function that integrated properly with Excel.

    I agree that PowerPoint is Word's delinquent cousin.
    My students are forbidden to use it. They have to make video instead, which they must appear in. Half they have to narrate live, rest narrate in the video.
    What do you teach?
    That the content is more important than the medium?
  • chris_basschris_bass Posts: 4,913
    TheBigBean wrote:
    Chris Bass wrote:
    TheBigBean wrote:
    You are excluding every possible error not the reason that you have an error. Eg. IF(B=0,0,A/B). With your formula it would result in 0 even if B was non zero, but A was an error feeding from elsewhere. Essentially, it's lazy and poor practice, and widely used.

    This brings us back vagiely on topic. It is relatively straightforward to learn best practice software coding, but Excel seems to mostly be a free for all, so it would be nice if there was some basic training available.

    what?! that isn't what iferror does (i was going back to what slowbike said):

    Syntax
    IFERROR(value, value_if_error)

    The IFERROR function syntax has the following arguments:

    value Required. The argument that is checked for an error.

    value_if_error Required. The value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

    I don't know how to write it any clearer. IFERROR excludes all errors (see emphasis above) not the reason for the error e.g. you might want to divide by zero. Restating my formula above as IFERROR(A/B,0) results in zero if A is a #VALUE! error (or anything else). That's not very helpful and not the original reason for including it.

    misunderstanding I think - i was just going back to slowbikes point about using iferror not if and then iserror inside the if which would effectively do the same as iferror in his case (assuming his wife's suggestion is valid of course!)
    www.conjunctivitis.com - a site for sore eyes
  • rjsterry wrote:
    rjsterry wrote:
    TheBigBean wrote:
    Word is fine*. Powerpoint is the enemy.

    *except it needs to allow infinite paper size, thus ending the use of Excel for tables full of text discussion.

    If it were fine it wouldn't make random unrequested changes to formatting, font and type size and would include a workable tables function that integrated properly with Excel.

    I agree that PowerPoint is Word's delinquent cousin.
    My students are forbidden to use it. They have to make video instead, which they must appear in. Half they have to narrate live, rest narrate in the video.
    What do you teach?
    That the content is more important than the medium?
    thats the one
Sign In or Register to comment.