Results 1 to 12 of 12

20110710, 18:01 #1
 Join Date
 Dec 2009
 Location
 Central Florida
 Posts
 1,499
 Thanks
 30
 Thanked 205 Times in 163 Posts
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 fillup.
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 WhoExperience is truly the best teacher.
Backup! Backup! Backup! GHOST Rocks!

20110710, 19:00 #2
 Join Date
 Jun 2010
 Location
 Portugal
 Posts
 12,479
 Thanks
 151
 Thanked 1,390 Times in 1,218 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.

20110711, 06:50 #3
 Join Date
 Dec 2009
 Location
 Central Florida
 Posts
 1,499
 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 WhoLast edited by DrWho; 20110711 at 07:41.
Experience is truly the best teacher.
Backup! Backup! Backup! GHOST Rocks!

20110711, 07:12 #4
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,422
 Thanks
 368
 Thanked 1,455 Times in 1,324 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


20110711, 17:54 #5
 Join Date
 Dec 2009
 Location
 Central Florida
 Posts
 1,499
 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 WhoExperience is truly the best teacher.
Backup! Backup! Backup! GHOST Rocks!

The Following User Says Thank You to DrWho For This Useful Post:
paulbyr (20110714)

20110714, 07:05 #6
 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; 20110714 at 07:24. Reason: added observation
Paulbyr in NC

20110714, 08:03 #7
 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

20110714, 08:21 #8
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,272
 Thanks
 3
 Thanked 187 Times in 173 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

20110714, 17:18 #9
 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

20110714, 18:17 #10
 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

20110721, 20:31 #11
 Join Date
 Dec 2009
 Location
 North Wales, UK
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 selfexplanatory 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 )
'Selfcommenting' 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!
NickLast edited by nick coope; 20110721 at 20:33.

20110722, 00:40 #12
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,272
 Thanks
 3
 Thanked 187 Times in 173 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