Reply to thread

Message

<blockquote data-quote="CRGreathouse" data-source="post: 1478311" data-attributes="member: 474">OK, let's start.&nbsp; I'm taking a fairly simple formula, as far as yours go, and making some kind of sense of it.&nbsp; This is the formula in cell D8.=1+4*(LOG((2^(TRUNC(LOG(D7,2)))),2))+TRUNC((((D7/(2^(TRUNC(LOG(D7,2)))))-1)*4))First, there are lots of extra parens in there.&nbsp; This makes me sad, since it's harder to read.&nbsp; (Also, in a fit of pique, I changed TRUNC to INT -- they work the same if you don't use the optional argument of TRUNC.)=1+4*(LOG(2^(Int(LOG(D7,2))),2))+Int(((D7/(2^(Int(LOG(D7,2)))))-1)*4)Next, there is no reason to take the (base 2) log of a power (of 2).=1+4*(Int(LOG(D7,2)))+Int(((D7/(2^(Int(LOG(D7,2)))))-1)*4)Distributing for reading ease:=1+4*(Int(LOG(D7,2)))+Int(4*(D7/(2^(Int(LOG(D7,2)))))-4)Simplifying, since Int (blah - 4) = Int (blah) - 4:=4*(Int(LOG(D7,2)))+Int(4*(D7/(2^(Int(LOG(D7,2))))))-3Now it's at least possible to figure out where this is going.&nbsp; The first term is pretty clear, but the second seems odd... with internal rounding, it's susceptable to unusual results from numbers with large round-off.&nbsp; While this may make sense when making a formula from a chart, there's no reason to come up with a formula that rigidly follows the space constraints on the chart.Note that this step changes the value by removing internal rounding.&nbsp; Without this step, the values will presumably follow the chart... but the chart rounds, and we don't like that in Excel.&nbsp; Plus, this will make everything look neater.=4*(Int(LOG(D7,2)))+Int(4*(D7/(2^(LOG(D7,2)))))-3With that done, there's another log of a power to remove:=4*(Int(LOG(D7,2)))+Int(4*(D7/D7))-3Obvious simplification then follows.=4*(Int(LOG(D7,2)))+1This is much better.&nbsp; Of course, we can go one step beyond -- there's no reason to round until the end:=Int(4*LOG(D7,2))+1This produces the exact same results from 1-22, and similar results (always within 1) outside that range.</blockquote>

[QUOTE="CRGreathouse, post: 1478311, member: 474"] OK, let's start. I'm taking a fairly simple formula, as far as yours go, and making some kind of sense of it. This is the formula in cell D8. =1+4*(LOG((2^(TRUNC(LOG(D7,2)))),2))+TRUNC((((D7/(2^(TRUNC(LOG(D7,2)))))-1)*4)) First, there are lots of extra parens in there. This makes me sad, since it's harder to read. (Also, in a fit of pique, I changed TRUNC to INT -- they work the same if you don't use the optional argument of TRUNC.) =1+4*(LOG(2^(Int(LOG(D7,2))),2))+Int(((D7/(2^(Int(LOG(D7,2)))))-1)*4) Next, there is no reason to take the (base 2) log of a power (of 2). =1+4*(Int(LOG(D7,2)))+Int(((D7/(2^(Int(LOG(D7,2)))))-1)*4) Distributing for reading ease: =1+4*(Int(LOG(D7,2)))+Int(4*(D7/(2^(Int(LOG(D7,2)))))-4) Simplifying, since Int (blah - 4) = Int (blah) - 4: =4*(Int(LOG(D7,2)))+Int(4*(D7/(2^(Int(LOG(D7,2))))))-3 Now it's at least possible to figure out where this is going. The first term is pretty clear, but the second seems odd... with internal rounding, it's susceptable to unusual results from numbers with large round-off. While this may make sense when making a formula from a chart, there's no reason to come up with a formula that rigidly follows the space constraints on the chart. Note that this step changes the value by removing internal rounding. Without this step, the values will presumably follow the chart... but the chart rounds, and we don't like that in Excel. Plus, this will make everything look neater. =4*(Int(LOG(D7,2)))+Int(4*(D7/(2^(LOG(D7,2)))))-3 With that done, there's another log of a power to remove: =4*(Int(LOG(D7,2)))+Int(4*(D7/D7))-3 Obvious simplification then follows. =4*(Int(LOG(D7,2)))+1 This is much better. Of course, we can go one step beyond -- there's no reason to round until the end: =Int(4*LOG(D7,2))+1 This produces the exact same results from 1-22, and similar results (always within 1) outside that range. [/QUOTE]

Verification