Results 1 to 12 of 12
  1. #1
    Bronze Lounger DrWho's Avatar
    Join Date
    Dec 2009
    Location
    Central Florida
    Posts
    1,501
    Thanks
    30
    Thanked 205 Times in 163 Posts

    Cool Purpose of double quotes in a formula?

    Hi,
    My first question here.
    I have a spreadsheet, that I found on the internet, for calculating my gas mileage per fill-up.
    In the last col where my cost per mile is calculated I have this formula:

    =IF(OR(C10="",E10=""),"",E10/C10)

    All I want is the result of the last part of the formula, where E10 is divided by C10.
    I don't understand the rest of the formula, especially all those repeated double quotes.

    Could anyone explain this formula to me?

    I did some Google searching for an explanation of those double quotes but found no explanation.

    So here I am.......a computer tech, but definitely NOT an Excel programmer.

    Dr Who
    Experience is truly the best teacher.

    Backup! Backup! Backup! GHOST Rocks!

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    It's just testing whether cells C10 or E10 have an empty value and if either of them has it, the result is an empty value. It only calculates E10/C10 if both cells are not empty.

  3. #3
    Bronze Lounger DrWho's Avatar
    Join Date
    Dec 2009
    Location
    Central Florida
    Posts
    1,501
    Thanks
    30
    Thanked 205 Times in 163 Posts
    Ok. So that's the purpose of the IF statement. (???)
    Like I said, I'm not an Excel programmer, so I didn't understand the significance of the IF statement and everything that went with it.
    That formula is calculating the cost per mile after a fill up, so the two values, money and gallons, would never be zero, not on this spreadsheet anyway.

    I believe I did mention that I didn't write this spreadsheet, and it came with a lot of extra stuff on it that I'm still trying to understand and in some cases remove.

    Thank you for your help. I've got it now.

    Dr Who
    Last edited by DrWho; 2011-07-11 at 08:41.
    Experience is truly the best teacher.

    Backup! Backup! Backup! GHOST Rocks!

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Dr. Who,

    If you look at the spreadsheet it most likely has that formula copied well down the rows. The purpose of the formula is to keep a #DIV error {divide by 0} from showing in the rows which have not yet been filled in. If you copy/fill the rows only one at a time then it is not necessary but surely doesn't hurt. It is considered good programming practice to trap errors such as this just to prevent users, like yourself unfamiliar with Excel, from getting an error which they don't understand.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following 2 Users Say Thank You to RetiredGeek For This Useful Post:

    paulbyr (2011-07-14),salty3dog (2011-07-23)

  6. #5
    Bronze Lounger DrWho's Avatar
    Join Date
    Dec 2009
    Location
    Central Florida
    Posts
    1,501
    Thanks
    30
    Thanked 205 Times in 163 Posts
    I didn't know how to even think about it, in such eloquent terms, but you're right.
    The formula for each col. has been continued down the col's to at least line 100.
    And each formula has that protection built in. So I won't try to remove them, but
    just understand them.

    Thank you for your most enlightening explanation.

    Dr Who
    Experience is truly the best teacher.

    Backup! Backup! Backup! GHOST Rocks!

  7. The Following User Says Thank You to DrWho For This Useful Post:

    paulbyr (2011-07-14)

  8. #6
    Lounger
    Join Date
    Oct 2010
    Location
    Raleigh, NC, USA
    Posts
    45
    Thanks
    36
    Thanked 4 Times in 4 Posts
    Thanks Dr. Who for the exposure of a feature I had never used. Thanks also to RetiredGeek for calling my attention to it. I had always done the more ponderous test for divide by zero by a blank.
    Forgive my memory - I was trying to remember IF coding from a year ago. Basically I test the division and if the divisor cell is blank (it contains " ") then the IF test is true and you get a blank (" ") as the answer. Otherwise, the IF test is false and you get the answer to the division.

    Ok, I also learned that "" is interpreted (by EXCEL) the same as " ". I always just inserted the blank character. As Dr. Who said, the test avoids the divided by zero error. I never cared if the numerator was blank or not but more complex SSs might have a number in the denominator and a blank in the numerator. btw, I am an old FORTRAN programmer so I use that kind of IF structure.
    Last edited by paulbyr; 2011-07-14 at 08:24. Reason: added observation
    Paulbyr in NC

  9. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    FYI: "" is not the same as " " and will not be interpreted the same by Excel and can yield different results depending on how the formula is constructed. With the recommended formula, a space (" ") in the cell will yield the divide by zero error since it will not be equal to the null string ("").

    Steve

  10. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You could check for two numbers using:
    =IF(COUNT(C10,E10)=2,E10/C10,"")
    though it would not check for 0 in the divisor. You could cater for that with:
    =IF(AND(COUNT(C10,E10)=2,C10<>0),E10/C10,"")
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #9
    Lounger
    Join Date
    Oct 2010
    Location
    Raleigh, NC, USA
    Posts
    45
    Thanks
    36
    Thanked 4 Times in 4 Posts
    Steve, I will have try this out - like I said before, I think I have always used " " to test for an empty cell. If " " was testing an empty cell, the result was true and if it was false, the content of the cell was accepted.

    With your 9507 posts looking at me, I am certainly not challenging you - I just want to learn what won't work for me. BTW, I am still using EXCEL 2003.
    Paulbyr in NC

  12. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I would be surprised that if any versions of XL, a comparison of a blank cell to a space (eg: = A1 = " ") would be true. It was not in any of the XL versions I have used (XL97, XL2000, XL2002, XL2010), but a an empty cell was equal to a null (=A1="" would be true).

    Perhaps the cells were not blank but contained a space (either entered, or the result of a formula...)

    Steve

  13. #11
    New Lounger
    Join Date
    Dec 2009
    Location
    North Wales, UK
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    You could check for two numbers using:
    =IF(COUNT(C10,E10)=2,E10/C10,"")
    though it would not check for 0 in the divisor. You could cater for that with:
    =IF(AND(COUNT(C10,E10)=2,C10<>0),E10/C10,"")
    Dr Who,

    The IF function is used in a formula of the form:
    =IF( logical test, true answer, false answer)
    where the logical test can be any expression that evaluates to TRUE or FALSE (eg A1>B1). If the result of the test is true, then true answer is placed in the cell, otherwise false answer is used. Either of these can be any expression (eg, in your case, "" [an empty text string] and A1/B1 [a calculation]).

    Since the writer of this spreadsheet seems to have given you a whole column of formulae to go at, you might well have been surprised to find that it suddenly stopped working after 100 tank refills, as the IF formula doesn't easily reveal its presence (eg, by a #DIV/0! error without the IF test) unless you click on the cell and know where to look!

    ------------------

    Rory,

    Since there is a multitude of possible values that could exist in the numerator and denominator cells which would give an error on division, a simpler and more self-explanatory possibility is to use the ISERROR function to catch all errors. For example,
    =IF( ISERROR( A1 / B1 ), "", A1 / B1 )
    tests for the intended calculation first, and only if the result is error free is it entered into the cell. Otherwise the null (empty) string "" is returned. Obviously, a different value could be returned if there was an error, but it would probably be less useful than the Excel error code.

    Of course, if you only want to test for the possibility that the cell might be empty (because no value has yet been filled in), you could use the ISBLANK function:
    =IF( ISBLANK( B1 ), "", A1 / B1 ) [it is only necessary to test the denominator] or
    =IF( AND( ISBLANK( A1 ), ISBLANK( B1 ) ), "", A1 / B1 ) [if you specifically want to test for both being empty].

    If you want to avoid just the division by zero in any of its forms (eg, an empty cell, the value 0, the result of a formula that gives zero, etc) then try the test:
    =IF( B1 = 0 , "", A1 / B1 )

    'Self-commenting' forms (eg, ISERROR, ISBLANK) are generally more helpful when you come to edit the spreadsheet some time later when you have forgotten why you did things - as little as a week in my case!

    Nick
    Last edited by nick coope; 2011-07-21 at 21:33.

  14. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Nick,That's true but it's a matter of preference. I generally don't like iserror precisely because it is too general. I'd rather test for anticipated problems and get a warning of other errors.Isblank assumes your test cell is a direct entry cell, which may be the case here, but a test for "" is often safer.As for self documenting functions, I agree to an extent but not at the expense of utility or safety. I also wouldn't put this in the category of a function requiring documentation but, again, that's personal (hence this question!).Horses for courses.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •