I made a spreadsheet to combine f/stop, shutter speed, and film speed into a
single number. This number represents how many stops the exposure was taken
from a sunny 16 setting. For example, if I'm using ISO 100 film, a shutter
speed of 1/125 sec., and an f/stop of 11, it will calculate to about .8. Ok,
now for the fun part. When I enter a shutter speed into a cell, I just input
the denominator because all the numbers are just reciprocals of their
respective shutter speeds. For example, for 1/125 sec, I enter 125 and let
the inner workings take care of it. The problem is when the shutter speeds
are in the 1 second or more neighborhood, I have to be able to differentiate
between 1/4 of a second and 4 seconds. So I use the same syntax as what the
camera displays. 4 seconds is displayed as 4". For 7/10 of a second, the
display shows 0"7. For this type of format, I will just type 7/10 in the
cell. Here is the part of the equation that is messing up the works:

=IF(ISERROR(2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2)),2*LOG(16/F40)/LOG(2)+LOG($C$2*(MID(E40,1,FIND("""",E40)-1)))/LOG(2),2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2))

And just to simplify things, here is just the middle part of the expression
which does the evalutation when the quote sign is present:
2*LOG(16/F40)/LOG(2)+LOG($C$2*(MID(E40,1,FIND("""",E40)-1)))/LOG(2)

F40 is the f/stop
E40 is the shutter speed
C2 is the ISO of the film.

Now when this function evaluates to an error because of the quote sign in
the cell, it takes the alternate path of extracting the numbers before the
quote sign. But if the number is 7/10", or 7/10 of a second, Excel is
interpreting this as a date and the output is not as expected. If I format
the cell as a fraction, it doesn't matter. I'm not sure why Excel even
accepts the fraction format when 7/10" isn't a fraction anyway. Any ideas
how to set this matter straight?

Thanks for your replies.

David Farber
L.A., CA

On Mon, 16 Nov 2009 17:07:55 -0800, "David Farber"
wrote:

I made a spreadsheet to combine f/stop, shutter speed, and film speed into a
single number. This number represents how many stops the exposure was taken
from a sunny 16 setting. For example, if I'm using ISO 100 film, a shutter
speed of 1/125 sec., and an f/stop of 11, it will calculate to about .8. Ok,
now for the fun part. When I enter a shutter speed into a cell, I just input
the denominator because all the numbers are just reciprocals of their
respective shutter speeds. For example, for 1/125 sec, I enter 125 and let
the inner workings take care of it. The problem is when the shutter speeds
are in the 1 second or more neighborhood, I have to be able to differentiate
between 1/4 of a second and 4 seconds. So I use the same syntax as what the
camera displays. 4 seconds is displayed as 4". For 7/10 of a second, the
display shows 0"7. For this type of format, I will just type 7/10 in the
cell. Here is the part of the equation that is messing up the works:

=IF(ISERROR(2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2)),2*LOG(16/F40)/LOG(2)+LOG($C$2*(MID(E40,1,FIND("""",E40)-1)))/LOG(2),2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2))

And just to simplify things, here is just the middle part of the expression
which does the evalutation when the quote sign is present:
2*LOG(16/F40)/LOG(2)+LOG($C$2*(MID(E40,1,FIND("""",E40)-1)))/LOG(2)

F40 is the f/stop
E40 is the shutter speed
C2 is the ISO of the film.

Now when this function evaluates to an error because of the quote sign in
the cell, it takes the alternate path of extracting the numbers before the
quote sign. But if the number is 7/10", or 7/10 of a second, Excel is
interpreting this as a date and the output is not as expected. If I format
the cell as a fraction, it doesn't matter. I'm not sure why Excel even
accepts the fraction format when 7/10" isn't a fraction anyway. Any ideas
how to set this matter straight?

Thanks for your replies.

David Farber
L.A., CA


David,

I don't really understand your formula. But one way to ensure that the value
in E40 is properly converted would be to use a User Defined Function, at least
for that part of the formula.

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

You can then use Eval(E40) wherever you are evaluated E40 for a value.

You might be able to simplify your function to:

=IF(ISERROR(-E40),
2*LOG(16/F40)/LOG(2)+LOG($C$2*(Eval(E40)))/LOG(2),
2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2))

=================================
Function Eval(s As String) As Double
Eval = Evaluate(Replace(s, """", ""))
End Function
====================================



--ron