D
dede
our professor is asking us the difference between a dumb formula and a smart
formula and give an example. Any ideas?
formula and give an example. Any ideas?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
RagDyer
I've never explicitly heard of that before.
Maybe he means a formula that contains some type of error checking as being
"smart".
=A1/B1
As opposed to:
=IF(B1,A1/B1,"B1 Lacks Value")
Maybe he means a formula that contains some type of error checking as being
"smart".
=A1/B1
As opposed to:
=IF(B1,A1/B1,"B1 Lacks Value")
B
Biff
Hi!
The concept is really open to interpretation!
In my opinion a "smart" formula is one that accounts for all the possible
things that might cause the formula to fail. Fail meaning, returning
incorrect results.
A very simple "dumb" formula is:
=VLOOKUP(A1,Sheet2!A1:B100,2,0)
I'll let you figure out why it's "dumb"!
Biff
The concept is really open to interpretation!
In my opinion a "smart" formula is one that accounts for all the possible
things that might cause the formula to fail. Fail meaning, returning
incorrect results.
A very simple "dumb" formula is:
=VLOOKUP(A1,Sheet2!A1:B100,2,0)
I'll let you figure out why it's "dumb"!
Biff
B
Bernd Plumhoff
Hi dede,
If I interprete smart=efficient and dumb=not_as_efficient then this might be
your example:
Task: Count the occurences of Thursdays between 1-Jan-2005 and 26-Jan-2005.
"Dumb" function:
=SUM(--(WEEKDAY(ROW(INDIRECT(1/1/5&":"&1/26/5)))=4))
"Smart" function (by Daniel M.):
=INT((1/26/5-WEEKDAY(1/26/5+1-4)-1/1/5+8)/7)
Both functions calculate the correct result 4. But the "dumb" function needs
about 20x more execution time (due to FastExcel) since it first generates
the whole date sequence and counts all occurences then.
HTH,
Bernd
If I interprete smart=efficient and dumb=not_as_efficient then this might be
your example:
Task: Count the occurences of Thursdays between 1-Jan-2005 and 26-Jan-2005.
"Dumb" function:
=SUM(--(WEEKDAY(ROW(INDIRECT(1/1/5&":"&1/26/5)))=4))
"Smart" function (by Daniel M.):
=INT((1/26/5-WEEKDAY(1/26/5+1-4)-1/1/5+8)/7)
Both functions calculate the correct result 4. But the "dumb" function needs
about 20x more execution time (due to FastExcel) since it first generates
the whole date sequence and counts all occurences then.
HTH,
Bernd
R
Richard Buttrey
our professor is asking us the difference between a dumb formula and a smart
formula and give an example. Any ideas?
It really depends, as others have said, on your prof's definition of
dumb and smart.
I remember seeing an adult class of spreadsheet beginners, being shown
that the way to add the numbers in A1:A3 was to write A1+A2+A3, rather
than the rather obvious =SUM(A1:A3)
I suppose you could argue that the latter formula is smart since you
can add rows in between 1&3 without upsetting the accuracy of the
result.
Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
B
Biff
I'm feeling really dumb right now!
As written, neither of those formulas work. If I replace the date strings
with either cell references or use an addional function, Datevalue (which
would be dumb!), then they do work. The first formula is an array and can be
made a non-array by using Sumproduct.
The second formula is very nice (Daniel M. always comes up with these slick
date formulas!) but............
How (why?) does it work?
The only part of it that I understand is /7!
Please explain the logic..........
WEEKDAY(1/26/5+1-4)
Why do you add 1 then subtract 4? What is the significance of that? What
does that mean?
Same thing for:
1/1/5+8
Why do you add 8 to the start date? What is the significance of that? What
does that mean?
Here's another thing to consider regarding the topic of this thread: How
does one know if their solution is "dumb" or "smart" ?
Is it "dumb" to use formulas you don't understand?
Biff
=SUM(--(WEEKDAY(ROW(INDIRECT(1/1/5&":"&1/26/5)))=4))
=INT((1/26/5-WEEKDAY(1/26/5+1-4)-1/1/5+8)/7)
As written, neither of those formulas work. If I replace the date strings
with either cell references or use an addional function, Datevalue (which
would be dumb!), then they do work. The first formula is an array and can be
made a non-array by using Sumproduct.
The second formula is very nice (Daniel M. always comes up with these slick
date formulas!) but............
How (why?) does it work?
The only part of it that I understand is /7!
Please explain the logic..........
WEEKDAY(1/26/5+1-4)
Why do you add 1 then subtract 4? What is the significance of that? What
does that mean?
Same thing for:
1/1/5+8
Why do you add 8 to the start date? What is the significance of that? What
does that mean?
Here's another thing to consider regarding the topic of this thread: How
does one know if their solution is "dumb" or "smart" ?
Is it "dumb" to use formulas you don't understand?
Biff
B
Bernd Plumhoff
Hi Biff,
Was working with my German Excel version:
1/26/2005 should be the date constant 26-Jan-2005. Please enter the constant
38378.
1/1/2005 should be 1-Jan-2005. Please enter 38353.
Regards,
Bernd
Was working with my German Excel version:
1/26/2005 should be the date constant 26-Jan-2005. Please enter the constant
38378.
1/1/2005 should be 1-Jan-2005. Please enter 38353.
Regards,
Bernd