›
This forum is in permanent archive mode. Our new active community can be found here.

Newbie help in Excel

kiwi_birdkiwi_bird
edited May 2008 in Technology
I have never really used Excel before but am no forced to. My first question would be, why will the following function not work:

=if(Sqrt(C3)=Sqrt(B3), "Yes")

Thanks in advance!

Comments

  • lackofcheeselackofcheese
    edited May 2008
    It does for me; I guess it's my newer version of Excel.
    I speculate that in older versions of Excel you also need to specify a value for it to take when the expression is false, not just true?

    Incidentally, what's the point of comparing the square roots of two numbers? Why not compare the numbers themselves? (the principal square root is a one-to-one function)
    Post edited by lackofcheese on
  • kiwi_birdkiwi_bird
    Well, here's the problem which I'm supposed to solve:

    c) Extracting the square root from a six-digit number. The number of correct digits written will be rated as follows:

    One point for the first digit given correctly, two points for the second digit given correctly, …, 8 points for the eight digit given correctly.
    This means that for a solution correct to:
    • one digit, 1 point will be given
    • two digits, 3 (=1+2) points will be given
    • three digits, 6 (=1+2+3) points will be given
    • eight digits, 36 (=1+2+3+4+5+6+7+8) points will be given

    The maximum number of digits, for which points are given, is 8 for each problem - so the maximum of total points in this category is 360 (=ten problems with 36 points for 8 correct digits).

    In order to decide how many digits are given correctly, the following rule applies:
    Let A be the answer written down by a participant and B the exact solution. Then let N be the maximal integer for which |A-B|≤5∙102-N holds. Then the number of digits regarded as being correct is N, but no more than 8 or the number of digits written down by the participant.
    I know it's a horrible English translation. So anyway, I'm supposed to write a Excel table which automatically compares the correct square root with the one given as the answer and then tells you how many points are to be given.
  • lackofcheeselackofcheese
    edited May 2008
    Presumably the question should be
    Let A be the answer written down by a participant and B the exact solution. Then let N be the maximal integer for which |A-B|≤5∙10^(2-N) holds. Then the number of digits regarded as being correct is N, but no more than 8 or the number of digits written down by the participant.
    So what you need to do is to actually do the |A-B|≤5∙10^(2-N) comparison for the different values of N.

    I've made some Excel that works for one problem; it was pretty easy but I can't be bothered making it do 10 at once / sequentially. However, of course, I won't give it to you.
    Post edited by lackofcheese on
  • kiwi_birdkiwi_bird
    edited May 2008
    Well, it's up to you. I'm on work experience and bored to death. I just can't get this one task done. Well, back to translating then.
    Post edited by kiwi_bird on
  • lackofcheeselackofcheese
    edited May 2008
    If this is some kind of assessed educational task I don't want to help out too much.

    |A-B|≤5∙10^(2-N) is the crucial thing you need to check, though I'd say the fact that it's limited to the number of digits written by the participant is actually harder to test for.
    with Excel, that translates to
    ABS(A-B)<=5*10^(2-N)
    where A, B, and N are the cells, though you can use naming in Excel to replace the cell numbers with names like A B and N
    Post edited by lackofcheese on
  • kiwi_birdkiwi_bird
    You don't have to worry about it being an educational task, since we never even had Excel in school. ^_~

    But did I get it right: N is the number of points one gets?
  • lackofcheeselackofcheese
    edited May 2008
    Well, N is the number of points you get for meeting the conditions that:
    • |A-B|≤5∙10^(2-N) holds
    • The participant wrote at least N digits.
    However, if, say, you meet the conditions for N=3, but not N=4, you must also have met them for N=2 and N=1, so you will get 6 points.

    One notable issue with Excel in regard to this question:
    If you put in 400.00000 as the square root for 160000, that will be recorded as 400, which means it will count as 3 digits instead of the 8 it's supposed to be.
    Post edited by lackofcheese on
  • kiwi_birdkiwi_bird
    Wow, I don't understand this at all. I'll just sit down and rethink..
  • lackofcheeselackofcheese
    edited May 2008
    Also, with regards to the aforementioned issue of 400.00000 being recorded as 400, a way to avoid that is to force the cell storing the participant's answer to be text.
    I guess that means they can't (unless they're stupid and will get it entirely wrong) write less than 3 digits in an answer, because even if they guess at the number of hundreds, they must still put two zeroes after the answer. Hence the answer of 400 the aforementioned question is 3 digits correct; 400.0 is 4, 400.00 is 5, etc. ?

    I will try to explain the |A-B|≤5∙10^(2-N) thing now:-
    Let's take an example, the number is 100000
    Consequently, the correct answer with 9 digits of accuracy is
    316.227766
    Let's say the participant writes
    316.2281

    Then the error is 0.000334

    The criteria for the Nth digit to be correct is that the error is within a range of +-5 on the digit after it. That's what the |A-B|≤5∙10^(2-N) actually means.

    Now we'll check each digit one by one:-
    1) In this case, we're checking the hundreds. The criteria given is that for the hundreds to be correct, the error must be within +-50. The answer is correct here.
    +1pt
    2) Checking the tens, the error is within +-5, so it's fine.
    +2pts
    3) Checking ones, the error is within +-0.5, so it's fine.
    +3pts
    4) Checking 0.1s, the error is within +-0.05, so it's fine.
    +4pts
    5) Checking 0.01s, the error is within +-0.005, so it's fine.
    +5pts
    6) checking 0.001s, the error is within +-0.0005, so it's fine
    +6pts
    7) checking 0.0001s, the error is not within +-0.00005, so they fail here, and hence also on the 8th.

    Hence the total score is 1+2+3+4+5+6 = 21pts



    A more advanced way to do this would be to take the logarithm of the error, with some manipulation, but if you don't understand that approach, don't do it that way.
    Post edited by lackofcheese on
  • kiwi_birdkiwi_bird
    N≤2*lg(10/N) ?
  • HMTKSteveHMTKSteve
    Can't you force Excel to use a numbering format with X decimal places?

    Right-Click on the cell
    Select "Format Cells"
    Set your category to "Number"
    Set the "Decimal Places" to however many you want

    I have not use Excel in years but we have it on the work computers.
  • kiwi_birdkiwi_bird
    Yes, I've already forced it to 8 decimal points.
  • lackofcheeselackofcheese
    edited May 2008
    N≤2*lg(10/N) ?
    Nope.

    If you force it to 8 decimal places, then you can't meet the requirement of "Then the number of digits regarded as being correct is N, but no more than 8 or the number of digits written down by the participant.", since the number written down will now be 8.

    Also, they say 8 digits, hence it's 5 decimal places because there's 3 digits before the decimal point.
    Post edited by lackofcheese on
Sign In or Register to comment.