[RioRico]

This may belong here in LENSES, or maybe somewhere else. Whatever. Anyway, the algebra portion of my brain seems to have rusted away. Here's my perplexity:

I use a couple spreadsheets to track my lenses. On one, I compare the widest aperture of various primes at common focal lengths with the widest aperture for my most-used zooms at those focal lengths. I'd like to simply calculate the difference in f-stops. Don't ask why.

Here, row 12 is for 55mm. D12 shows f/5.6 for the 18-55. E12 shows f/4.5 for the 18-250. F12 shows f/3.3 for a crappy 40-80. And K12 shows f/1.8 for a Super-Tak prime.

I can look at those numbers and judge that the Tak is 3.33 stops faster than the 18-55, is 2.66 stops faster than the 18-250, and is 1.66 stops faster than the 40-80. Fine.

But I want to automate the calculation. So, my problem: what is a simple formula that I can plug into the spreadsheet, that computes the difference in f-stops between two different apertures? Yes, I know that the "f-stop scale... is an approximately geometric sequence of numbers that corresponds to the sequence of the powers of the square root of 2." But I just can't wrap my poor brain around an algorithm for finding the difference in f-stops between steps in that sequence. Duh.

Is there an algebraist in the house?

[Marc Sabatella]

Can your spreadsheet do log2? If so, log 2 (((larger f-number) / (smaller f-number)) ** 2) would seem to do it. Once upon a time I could have given you all sorts of alternatives, but I've forgotten most of what I ever knew abut math.

[RioRico]

Can your spreadsheet do log2? If so, log 2 (((larger f-number) / (smaller f-number)) ** 2) would seem to do it. Once upon a time I could have given you all sorts of alternatives, but I've forgotten most of what I ever knew abut math.

Yup, that does it! I'm using OpenOffice.Org Calc, which has a LOG(number;base) function. I input it as =LOG(((f1/f2)^2);2). So if I enter =LOG(((5.6/1.4)^2);2) it returns 4, which is just right. Thanks so much!

[/me returns to exciting drudgery]

[bdery]

Square the aperture value, round it, when make a ratio.

for instance, f2.8 yields 2.8*2.8 = 7.74, rounded = 8. f4 yields 4*4 = 16. 16 / 8 = 2 so you doubled the area. So that's one stop.

f16 yields 16*16 = 256. 256 / 8 = 32. 32 is 2^5 so you gained 5 stops.

Does that help? Otherwise, you could just use a lot of "IF" in sequence, but I doubt Excel can handle that much, you'd have to cover a lot of variations.

EDIT : Actually, Mark's way is simpler, mine is more of an explanation than an alrogithm