# Thread: Purpose of double quotes in a formula?

1. ## 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

2. 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. 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

4. 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.

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

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

6. 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

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

paulbyr (2011-07-14)

8. 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.

9. 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. 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,"")

11. 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.

12. 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. Originally Posted by rory
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:
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

14. 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.

#### Posting Permissions

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