Conditional Format for Doubling Values

[andrewwglenn]

I am performing an analysis where I have a value that grows at an unknown, changing rate. My example has two columns. The first is periods of time (t), incremented by 1. The second contains the increasing value. The value (i) at t0 = 1. For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]0[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1.05[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1.07[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1.16[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1.34[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1.87[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2.15[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2.41[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]3.36[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]4.10[/TD]
[/TR]
</tbody>[/TABLE]

Assuming the above table is positioned at A1:B10, how can I write one formula that will format all cells where the value is the first that has passed a "doubling" of i (in this case: 2, 4, 8, etc.), or where: x (is the first value that is) >= i * 2^n?

In my example, t = 6, 9 would be considered TRUE.

I hope this question is clear. Currently, I have 10 conditional expressions to account for the first 10 "doublings", but I would like to perform the operation with a single formula, if possible (probably is).


Cheers!

Andrew

 

[Joe4]

Not sure if this helps, but here is a Conditional Formatting formula that I cam up with that will toggle the formatting every time it doubles:

=MOD(INT(LOG($B1,2)),2)=1

So, in your example, the first six entries (0-5) would not be formatted,
entries 6-8 would be formatted,
and entry 9 would not be formatted,
etc.

 

[andrewwglenn]

Thanks for the example, Joe. I didn't think of this option, as I was hoping to format only the individual records that met the criteria, but I am not too picky. This should be sufficient for my analytic needs.

If anyone else has thoughts on how to accomplish the original example, I would like to understand my options, but for now, I will run with Joe4's suggestion.


Cheers!

 

[Joe4]

If you only want to highlight the rows where the doubling first exists (i.e. entries 6 and 9 in your example),
then starting your Conditional Formatting on row 2, use this formula:

=INT(LOG($B2,2)) > INT(LOG($B1,2))

and choose your formatting option.

Is this more of what you were looking for?

 

[andrewwglenn]

This works perfectly, Joe. I greatly appreciate your help.

In my application, the base of the log will be dynamic, so I updated the 2's in your formula to my variable. I have always wondered how to achieve this. Thanks for the tip!


Cheers.

 

[Joe4]

You are welcome.
Glad we were able to get you what you need!

In my application, the base of the log will be dynamic, so I updated the 2's in your formula to my variable.

Yep, that is how you would do it!