Wrestling with fractions and dates. Knowledge of photography is helpful.

[David Farber]

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

 

[Ron Rosenfeld]

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

 

[David Farber]

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

Hi Ron,

I really don't understand the ISERROR condition, -E40. Is that some trick to
test if there is a number in the cell?

I entered the code, =2*LOG(16/F40)/LOG(2)+LOG($C$2*(Eval(E40)))/LOG(2) in a
cell along with 2" in E40 and 13 in F40. I then entered your three lines of
VB code in the VB editor. The result in the cell is now, #NAME?

I'm not sure how to proceed from here.

Thanks for your reply.

 

[David Farber]

Hi Ron,

I really don't understand the ISERROR condition, -E40. Is that some
trick to test if there is a number in the cell?

I entered the code,
=2*LOG(16/F40)/LOG(2)+LOG($C$2*(Eval(E40)))/LOG(2) in a cell along
with 2" in E40 and 13 in F40. I then entered your three lines of VB
code in the VB editor. The result in the cell is now, #NAME?
I'm not sure how to proceed from here.

Thanks for your reply.

Something happened after I gave up trying to figure out the code. I loaded
an unrelated spreadsheet on top of the one in question. Then I closed the
unrelated one. Now the #NAME? error is gone and I think the correct numeric
value is displayed. Was there some extra step I was supposed to do after
entering the VB code to get it to run?

Thanks for your reply.

 

[Ron Rosenfeld]

Something happened after I gave up trying to figure out the code. I loaded
an unrelated spreadsheet on top of the one in question. Then I closed the
unrelated one. Now the #NAME? error is gone and I think the correct numeric
value is displayed. Was there some extra step I was supposed to do after
entering the VB code to get it to run?

Thanks for your reply.

Just entering the VBA code will not trigger a calculation event. So the #NAME!
error you see before entering the VBA code will persist until you do something
that triggers a calculation event.
--ron

 

[David Farber]

Just entering the VBA code will not trigger a calculation event. So
the #NAME! error you see before entering the VBA code will persist
until you do something that triggers a calculation event.
--ron

Hi Ron,

I must have changed the data in one of the other cells to trigger that.

I'm still curious to know the theory behind the (-E40) of the ISERROR code:
=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))

I was thinking that putting a quote mark in a division problem i.e.: 100/2"
would trigger the error and force it to convert the string to a number. Is
your shortcut saying that you can't take the opposite of 2" (for example)
because it's not a number, thus triggering the error condition?

Thanks for your reply.

 

[Ron Rosenfeld]

I'm still curious to know the theory behind the (-E40) of the ISERROR code:
=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))

I was thinking that putting a quote mark in a division problem i.e.: 100/2"
would trigger the error and force it to convert the string to a number. Is
your shortcut saying that you can't take the opposite of 2" (for example)
because it's not a number, thus triggering the error condition?

Your original error test was:

ISERROR(2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2))

to decide which branch of the IF statement to take. But this error condition
really only depends on E40 being text or a number. So that is really the only
cell you need to check.

Performing an arithmetic operation on a value will produce an error if the
value is not numeric. So that's what I did by "negating" the value.

One could consider using ISTEXT or ISNUMBER, but if the value is TEXT, but
Excel could interpret the value as a number, this might give undesired results,
in your particular situation where the value will either be or not be followed
by a quote.

It is certainly true that your initial error statement will return a useful
result, but I prefer shorter statements when appropriate.
--ron

 

[David Farber]

Your original error test was:

ISERROR(2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2))

to decide which branch of the IF statement to take. But this error
condition really only depends on E40 being text or a number. So that
is really the only cell you need to check.

Performing an arithmetic operation on a value will produce an error
if the value is not numeric. So that's what I did by "negating" the
value.

One could consider using ISTEXT or ISNUMBER, but if the value is
TEXT, but Excel could interpret the value as a number, this might
give undesired results, in your particular situation where the value
will either be or not be followed by a quote.

It is certainly true that your initial error statement will return a
useful result, but I prefer shorter statements when appropriate.
--ron

That's quite slick and compact!

Thanks.