Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If statement nested | Excel Discussion (Misc queries) | |||
Nested IF Statement Help | Excel Discussion (Misc queries) | |||
Nested if statement | Excel Worksheet Functions | |||
Nested IF Statement | Excel Discussion (Misc queries) | |||
Nested If statement | Excel Worksheet Functions |