I feel like I have seen a ghost.
I am building a spreadsheet to track and project fuel consumption. The user inputs the fuel capacity of the ship, the last reported fuel percentage, and the number of days spent underway since the last report. The spreadsheet multiplies the days underway times a standard daily burn rate, subtracts that product from the last reported fuel levels and produces an estimate of the current fuel level. Pretty simple stuff, but very handy.
So while I was working on this I noticed that if I left the "days underway since last report" cell empty (to indicate that the ship hadn't gone anywhere), the spreadsheet was still telling me that I had burned a day's worth of fuel. And I was all like "wtf?"
The explanation I eventually came to was that in mathematical formulae, Excel treats an empty cell as a 1, not as a zero! Wtf?!
I am spooked.
(A) Why would it do that?
(B) Is there a setting somewhere where I can tell it to cut that shit out?
(C) If not, why not?!
Sent via BlackBerry from T-Mobile
Subscribe to:
Post Comments (Atom)
Yay, Excel gremlins. What functions are you using? Typically, at least with basic functions like SUM and AVERAGE, Excel treats empty cells as zeroes. It's probably specific to some rando function in your sheet.
ReplyDeletePRODUCT()
ReplyDeleteInteresting. The general approach here is to avoid having a blank cell impact your equation (treating it as null). When you're adding or subtracting, this looks a lot like zero.
ReplyDeleteThe Product function is also treating the blank cell as a null input, so it's as if you're taking the product of the one cell (daily burn rate). Taking the product of one number is not very intuitive, but seems reasonable to multiply by one.
Strange. I tried PRODUCT() on an empty cell using default Excel settings and it returned 0 as you'd expect. Can you copy / paste the full cell formula into an email, provided it isn't classified or anything?
ReplyDeleteAh, good call -- I just reproduced what Amar is talking about above. So I guess it's a question of NULL being treated differently in different situations.
ReplyDeleteRules I have thusly identified:
1. NULL * NULL = 0
2. NULL * x = x where x = [variable non-zero float]
=PRODUCT(C2,B2)-PRODUCT(E2,514)
ReplyDeleteC2 is the reported percentage.
B2 is the fuel capacity of the ship.
E2 is the days underway input.
5000 is the estimated daily burn rate constant I am using.
So the first half converts the percentage into gallons, the second half subtracts the product of the daily burn constant and the number of days.
Sorry, 514 is the burn rate constant.
ReplyDeleteYep, Amar nailed it. Fill in those cells!
ReplyDeleteAmar's solution works, or just do * instead of product()!
ReplyDeleteRidic.
I figured out immediately that I could solve the problem by inputting a zero instead of leaving it blank. A solution isn't what I was looking for. Notice that none of my helpfully bullet-ed questions in the original post asked for a solution.
ReplyDeleteI was looking for an explanation! A justification! It seems to me the more reasonable default would be to treat a null input as a zero in a multiplication function. But even if it isn't, shouldn't it be a setting?
Guys this thread has been blowing the crap out of the email on my iphone it's freaking me out - my phone is yelling at me while i'm trying to study
ReplyDeleteSKIM GOT BUMBLED.
ReplyDeleteI actually don't think so. The way NULL values are treated depends on what application you're using or what language you're coding in, but they're intrinsically different than a number or a string because they represent the absence of information. I think specifying a set of rules for how NULL should be treated and sticking to it, without allowing any user input, significantly decreases confusion. Most importantly, it allows you to have a very specific understanding of how your VBA code will work if you start coding macros. If you could change it as a setting, it would be very difficult to make code work across varied machines, which defeats the purpose of having a standard language.
ReplyDeleteIgnore Sam, he isn't helping. :)
ReplyDeleteExcellent point about the compatibility concern. I am with you there.
ReplyDeleteI still think the treatment of null as 1 is...presumptuous!
Haha -- it is! It presumes that you want to treat NULLs as 1!
ReplyDeleteI do like that it's treated contextually, though.
NULL * NULL = 0
PRODUCT(NULL,NULL) = 0
NULL * x = 0
PRODUCT(NULL,x) = x
Hahaha presumptuous? You gave the comp an incomplete formula, so instead of presuming that you meant multiply by zero, the comp spat out your one input, untouched and in its original condition. :)
ReplyDeleteIt should produce an error and remind me to enter a number rather than presuming (presumptuously) that by entering nothing, I really intended to enter a 1.
ReplyDeleteI do not presume to presume that it is slightly presumptuous to assume that it presumed presumptuously? No?
ReplyDelete