Help with a nested IF statement

[DR]

Here is the statement
"IF(F13/F12>1,IF(I13>0,IF(C12>0,I13-C12,IF(C10>0,I13-C10,IF(C9>0,I13-C9,IF(C8>0,I13-C8,IF(C7>0,I13-C7,IF(C6>0,I13-C6,IF(C5>0,I12-C5,0))))))),0),I13-C13)"

I have too many statements.

Column C will have a time stamp. One time stamp in column C could apply to
several rows of data. The row count starts with 1, and is counted in column
F. Each row of data in Column I must reference the respective time stamp in
column C, and ignore all null values. Basically, if it's blank, it needs to
refer to the prior cell, until a time stamp is found. This is eating a lot of
IF statements.

Column I is the a secondary time stamp - if a cell in Column I is blank, the
corresponding value in Column H should be 0. This is working fine.

I have about 30 rows, and need to account for the possibility of only one
time stamp. Meanwhile, if other time stamps occur, they should be referenced,
instead of the first..

Is this making sense?

 

[Joel]

You could use a very simple UDF

Function DiffTime(Target As Range)

Set PreviousTime = Target.End(xlUp)
DiffTime = Target.Value - PreviousTime.Value
End Function


call with : =DiffTime(A5)

where the code will look up column A to find the last value and then
subtract the two numbers.

 

[DR]

This is above my head - I'm looking for info on creating a UDF.

Thanks

 

[Joel]

A UDF is just like a standard worksheet function/macro but you write it
yourself

1) Change security settting to medium. From worksheet menu Tools - Macro
-Security - set to mediium. When you open your workbook you need to enable
macros for code to work.
1) From worksheet type Alt-F11
2) From VBA menu - Insert - Module
3) Copy function in posting from the word "Function" to "end Function"
4) Now call function like any other worksheet function

enter line in worksheet
=DiffTime(A5)

Enter Function work VBA window.

Function DiffTime(Target As Range)

Set PreviousTime = Target.End(xlUp)
DiffTime = Target.Value - PreviousTime.Value
End Function

 

[JMB]

you could try

=LOOKUP(1E+100,C1:C12)

when you try to lookup a number that is greater than everything in C1:C12,
the lookup function returns the last number.

 

[DR]

Thanks - this worked well - and easily.
I need to learn UDFs...

It's amazing how little I feel I know after working with people on these
boards.

Thanks!!

 

[JMB]

you're welcome.

I know how you feel, but look at it as an opportunity to learn from some of
the best. The most useful techniques I know, including the lookup
suggestion, were learned from someone else on this newsgroup.