Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
dr dr is offline
external usenet poster
 
Posts: 29
Default Help with a nested IF statement

Here is the statement:
"IF(F13/F121,IF(I130,IF(C120,I13-C12,IF(C100,I13-C10,IF(C90,I13-C9,IF(C80,I13-C8,IF(C70,I13-C7,IF(C60,I13-C6,IF(C50,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?




  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default Help with a nested IF statement

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" wrote:

Here is the statement:
"IF(F13/F121,IF(I130,IF(C120,I13-C12,IF(C100,I13-C10,IF(C90,I13-C9,IF(C80,I13-C8,IF(C70,I13-C7,IF(C60,I13-C6,IF(C50,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?




  #3   Report Post  
Posted to microsoft.public.excel.programming
dr dr is offline
external usenet poster
 
Posts: 29
Default Help with a nested IF statement

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

Thanks

"Joel" wrote:

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" wrote:

Here is the statement:
"IF(F13/F121,IF(I130,IF(C120,I13-C12,IF(C100,I13-C10,IF(C90,I13-C9,IF(C80,I13-C8,IF(C70,I13-C7,IF(C60,I13-C6,IF(C50,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?




  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default Help with a nested IF statement

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


"DR" wrote:

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

Thanks

"Joel" wrote:

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" wrote:

Here is the statement:
"IF(F13/F121,IF(I130,IF(C120,I13-C12,IF(C100,I13-C10,IF(C90,I13-C9,IF(C80,I13-C8,IF(C70,I13-C7,IF(C60,I13-C6,IF(C50,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?




  #5   Report Post  
Posted to microsoft.public.excel.programming
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default Help with a nested IF statement

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" wrote:

Here is the statement:
"IF(F13/F121,IF(I130,IF(C120,I13-C12,IF(C100,I13-C10,IF(C90,I13-C9,IF(C80,I13-C8,IF(C70,I13-C7,IF(C60,I13-C6,IF(C50,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?






  #6   Report Post  
Posted to microsoft.public.excel.programming
dr dr is offline
external usenet poster
 
Posts: 29
Default Help with a nested IF statement

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" wrote:

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" wrote:

Here is the statement:
"IF(F13/F121,IF(I130,IF(C120,I13-C12,IF(C100,I13-C10,IF(C90,I13-C9,IF(C80,I13-C8,IF(C70,I13-C7,IF(C60,I13-C6,IF(C50,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?




  #7   Report Post  
Posted to microsoft.public.excel.programming
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default Help with a nested IF statement

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.


"DR" wrote:

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" wrote:

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" wrote:

Here is the statement:
"IF(F13/F121,IF(I130,IF(C120,I13-C12,IF(C100,I13-C10,IF(C90,I13-C9,IF(C80,I13-C8,IF(C70,I13-C7,IF(C60,I13-C6,IF(C50,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?




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
If statement nested k11ngy Excel Discussion (Misc queries) 5 October 13th 08 02:38 PM
Nested IF Statement Help Donnie Excel Discussion (Misc queries) 4 September 10th 08 09:04 PM
Nested if statement burl_h Excel Worksheet Functions 3 December 16th 06 07:35 PM
Nested IF Statement aposatsk Excel Discussion (Misc queries) 1 August 1st 06 05:16 PM
Nested If statement Jock W Excel Worksheet Functions 3 March 22nd 05 07:56 PM


All times are GMT +1. The time now is 04:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"