Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help Please
A row of 12 cells representing 12 months, all of which have an initial
value of zero. Each month a value is inserted into one cell, with the value remaining as each month passes. There is another cell in a separate worksheet into which I want to place the most recently entered value from the 12 cell row, i.e. it will overwrite the previous value. I've tried using 'if' statements and various formula listed in the Help files but without success. Can anyone offer a solution please? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help Please
hi,
not entirely sure what you are asking but if i understand.... if the worksheet is in the the same workbook then all you need to do is draw the value of the first sheet into the second sheets(or some variation there of...) on sheet 2 enter(in any cell) =sheet1!A1 'or the cell address desired. sheet 2 will update as sheet1!A1.value changes. you will need a formula for each of the 12 months. edit to fit your data. regards FSt1 "Ken" wrote: A row of 12 cells representing 12 months, all of which have an initial value of zero. Each month a value is inserted into one cell, with the value remaining as each month passes. There is another cell in a separate worksheet into which I want to place the most recently entered value from the 12 cell row, i.e. it will overwrite the previous value. I've tried using 'if' statements and various formula listed in the Help files but without success. Can anyone offer a solution please? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help Please
Hi Ken,
If I am not mistaken, you are looking for the last value <0 ... Please adapt to your needs ... =LOOKUP(2,1/(A1:L1<0),A1:L1) HTH |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help Please
Assuming your 12 monthly values are in A2:L2 on Sheet1, You can use this
formula in versions prior to Excel 2007: =IF(ISERROR(OFFSET(Sheet1!A2,0,COUNTIF(Sheet1!A2:L 2,"<0")-1,1,1)),0,OFFSET(Sheet1!A2,0,COUNTIF(Sheet1!A2:L2, "<0")-1,1,1)) In Excel 2007, you can use =IFERROR(OFFSET(Sheet1!A2,0,COUNTIF(Sheet1!A2:L2," <0")-1,1,1),0) "Ken" wrote in message ... A row of 12 cells representing 12 months, all of which have an initial value of zero. Each month a value is inserted into one cell, with the value remaining as each month passes. There is another cell in a separate worksheet into which I want to place the most recently entered value from the 12 cell row, i.e. it will overwrite the previous value. I've tried using 'if' statements and various formula listed in the Help files but without success. Can anyone offer a solution please? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help Please
In other words you want the *last* value in the range that is not 0?
Are there any negative numbers in the range? Are there any empty cells in the range? Are there any TEXT entries in the range? If the answer to all those questions is NO, try this: =IF(COUNTIF(A1:L1,0)<12,LOOKUP(2,1/(A1:L10),A1:L1),"") -- Biff Microsoft Excel MVP "Ken" wrote in message ... A row of 12 cells representing 12 months, all of which have an initial value of zero. Each month a value is inserted into one cell, with the value remaining as each month passes. There is another cell in a separate worksheet into which I want to place the most recently entered value from the 12 cell row, i.e. it will overwrite the previous value. I've tried using 'if' statements and various formula listed in the Help files but without success. Can anyone offer a solution please? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help Please
Thank you all for your responses.
FSt1 Entries in cells will not change HTH Your formula returns the error "A value is not available to the formula or function". Tyro The formula works very well . Thanks. If you have the time, and inclination, would you post a word description of the formula - I cannot follow it entirely. Biff The three answers are 'no', but your formula returns the same error "A value is not available to the formula or function". I tried to follow it through but stumbled on the double quotation marks inside the final parentheses. What do they mean? Should they have something between them? Regards, Ken |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help Please
"A value is not available to the formula or function".
Hmmm... I've never seen that error before so I really don't know what it means. the double quotation marks inside the final parentheses. What do they mean? They are used to return a blank *until* a number 0 is entered in the range. If you want some other result just replace the "" with whatever you want. Just remember that if you want some TEXT value returned to enclose the TEXT string in quotes like this: =IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"no data") If you want a 0 returned then you don't need the quotes: =IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),0) I've slightly changed the formula to: =IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"") Here's a very small sample file that demonstrates this: lastnum.xls 14kb http://cjoint.com/?bghmSfGDPm Enter some numbers (0) replacing the 0s and you'll see that it works. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Thank you all for your responses. FSt1 Entries in cells will not change HTH Your formula returns the error "A value is not available to the formula or function". Tyro The formula works very well . Thanks. If you have the time, and inclination, would you post a word description of the formula - I cannot follow it entirely. Biff The three answers are 'no', but your formula returns the same error "A value is not available to the formula or function". I tried to follow it through but stumbled on the double quotation marks inside the final parentheses. What do they mean? Should they have something between them? Regards, Ken |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help Please
You formula produces #N/A if all 12 entries are blank. He's referring to the
description of #N/A "T. Valko" wrote in message ... "A value is not available to the formula or function". Hmmm... I've never seen that error before so I really don't know what it means. the double quotation marks inside the final parentheses. What do they mean? They are used to return a blank *until* a number 0 is entered in the range. If you want some other result just replace the "" with whatever you want. Just remember that if you want some TEXT value returned to enclose the TEXT string in quotes like this: =IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"no data") If you want a 0 returned then you don't need the quotes: =IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),0) I've slightly changed the formula to: =IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"") Here's a very small sample file that demonstrates this: lastnum.xls 14kb http://cjoint.com/?bghmSfGDPm Enter some numbers (0) replacing the 0s and you'll see that it works. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Thank you all for your responses. FSt1 Entries in cells will not change HTH Your formula returns the error "A value is not available to the formula or function". Tyro The formula works very well . Thanks. If you have the time, and inclination, would you post a word description of the formula - I cannot follow it entirely. Biff The three answers are 'no', but your formula returns the same error "A value is not available to the formula or function". I tried to follow it through but stumbled on the double quotation marks inside the final parentheses. What do they mean? Should they have something between them? Regards, Ken |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help Please
You're getting an #N/A error from 2 of the formulas. Once again, the
formulas below presume your data is in A2:L2 on sheet1 and your formula is on some other sheet. Here is HRH's formula, the best of the formulas, adjusted to take care of that. For errors you can change the ,0 (comma 0) in the formulas to ,"" (comma empty string) to show a blank. All Excel versions: =IF(ISERROR(LOOKUP(2,1/(Sheet1!A2:L2<0),Sheet1!A2:L2)),0,LOOKUP(2,1/(Sheet1!A2:L2<0),Sheet1!A2:L2)) Excel 2007 only: =IFERROR(LOOKUP(2,1/(Sheet1!A2:L2<0),Sheet1!A2:L2),0) tyro "Ken" wrote in message ... A row of 12 cells representing 12 months, all of which have an initial value of zero. Each month a value is inserted into one cell, with the value remaining as each month passes. There is another cell in a separate worksheet into which I want to place the most recently entered value from the 12 cell row, i.e. it will overwrite the previous value. I've tried using 'if' statements and various formula listed in the Help files but without success. Can anyone offer a solution please? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help Please
Just tripped over this thread and would like to comment.
The first sentence of the OP states all cells have an *initial* value of zero. It seems that you're reading into the meaning of that sentence certain suppositions. The other responders are doing the same, making suppositions. Many threads here require guesses in order to make suggestions, where the OPs fail to include all requirements. Why are you acting as if *your* guesses are the only correct ones, and everyone else's is wrong. I read that the cells are *initially* filled with zeroes, which are returns of existing formulas. In which case *your best* formula FAILS! Many of us here come up with varying suggestions, dependant on our own understanding of the OPs request. In such cases, we simply state what differences our suggestions will take into consideration, compared to suggestions made by other responders. We don't berate their interpretation, we just state the distinctions, and await the OP's feed-back. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Tyro" wrote in message . net... You're getting an #N/A error from 2 of the formulas. Once again, the formulas below presume your data is in A2:L2 on sheet1 and your formula is on some other sheet. Here is HRH's formula, the best of the formulas, adjusted to take care of that. For errors you can change the ,0 (comma 0) in the formulas to ,"" (comma empty string) to show a blank. All Excel versions: =IF(ISERROR(LOOKUP(2,1/(Sheet1!A2:L2<0),Sheet1!A2:L2)),0,LOOKUP(2,1/(Sheet1 !A2:L2<0),Sheet1!A2:L2)) Excel 2007 only: =IFERROR(LOOKUP(2,1/(Sheet1!A2:L2<0),Sheet1!A2:L2),0) tyro "Ken" wrote in message ... A row of 12 cells representing 12 months, all of which have an initial value of zero. Each month a value is inserted into one cell, with the value remaining as each month passes. There is another cell in a separate worksheet into which I want to place the most recently entered value from the 12 cell row, i.e. it will overwrite the previous value. I've tried using 'if' statements and various formula listed in the Help files but without success. Can anyone offer a solution please? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help Please
I filled the cells with formulas that return 0 and the formula works fine.
"Ragdyer" wrote in message ... Just tripped over this thread and would like to comment. The first sentence of the OP states all cells have an *initial* value of zero. It seems that you're reading into the meaning of that sentence certain suppositions. The other responders are doing the same, making suppositions. Many threads here require guesses in order to make suggestions, where the OPs fail to include all requirements. Why are you acting as if *your* guesses are the only correct ones, and everyone else's is wrong. I read that the cells are *initially* filled with zeroes, which are returns of existing formulas. In which case *your best* formula FAILS! Many of us here come up with varying suggestions, dependant on our own understanding of the OPs request. In such cases, we simply state what differences our suggestions will take into consideration, compared to suggestions made by other responders. We don't berate their interpretation, we just state the distinctions, and await the OP's feed-back. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Tyro" wrote in message . net... You're getting an #N/A error from 2 of the formulas. Once again, the formulas below presume your data is in A2:L2 on sheet1 and your formula is on some other sheet. Here is HRH's formula, the best of the formulas, adjusted to take care of that. For errors you can change the ,0 (comma 0) in the formulas to ,"" (comma empty string) to show a blank. All Excel versions: =IF(ISERROR(LOOKUP(2,1/(Sheet1!A2:L2<0),Sheet1!A2:L2)),0,LOOKUP(2,1/(Sheet1 !A2:L2<0),Sheet1!A2:L2)) Excel 2007 only: =IFERROR(LOOKUP(2,1/(Sheet1!A2:L2<0),Sheet1!A2:L2),0) tyro "Ken" wrote in message ... A row of 12 cells representing 12 months, all of which have an initial value of zero. Each month a value is inserted into one cell, with the value remaining as each month passes. There is another cell in a separate worksheet into which I want to place the most recently entered value from the 12 cell row, i.e. it will overwrite the previous value. I've tried using 'if' statements and various formula listed in the Help files but without success. Can anyone offer a solution please? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help Please
You're right, I didn't realize you were referencing Row2.
As to the rest of the post ... it's nice to be nice! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Tyro" wrote in message et... I filled the cells with formulas that return 0 and the formula works fine. "Ragdyer" wrote in message ... Just tripped over this thread and would like to comment. The first sentence of the OP states all cells have an *initial* value of zero. It seems that you're reading into the meaning of that sentence certain suppositions. The other responders are doing the same, making suppositions. Many threads here require guesses in order to make suggestions, where the OPs fail to include all requirements. Why are you acting as if *your* guesses are the only correct ones, and everyone else's is wrong. I read that the cells are *initially* filled with zeroes, which are returns of existing formulas. In which case *your best* formula FAILS! Many of us here come up with varying suggestions, dependant on our own understanding of the OPs request. In such cases, we simply state what differences our suggestions will take into consideration, compared to suggestions made by other responders. We don't berate their interpretation, we just state the distinctions, and await the OP's feed-back. -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Tyro" wrote in message . net... You're getting an #N/A error from 2 of the formulas. Once again, the formulas below presume your data is in A2:L2 on sheet1 and your formula is on some other sheet. Here is HRH's formula, the best of the formulas, adjusted to take care of that. For errors you can change the ,0 (comma 0) in the formulas to ,"" (comma empty string) to show a blank. All Excel versions: =IF(ISERROR(LOOKUP(2,1/(Sheet1!A2:L2<0),Sheet1!A2:L2)),0,LOOKUP(2,1/(Sheet1 !A2:L2<0),Sheet1!A2:L2)) Excel 2007 only: =IFERROR(LOOKUP(2,1/(Sheet1!A2:L2<0),Sheet1!A2:L2),0) tyro "Ken" wrote in message ... A row of 12 cells representing 12 months, all of which have an initial value of zero. Each month a value is inserted into one cell, with the value remaining as each month passes. There is another cell in a separate worksheet into which I want to place the most recently entered value from the 12 cell row, i.e. it will overwrite the previous value. I've tried using 'if' statements and various formula listed in the Help files but without success. Can anyone offer a solution please? |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help Please
You formula produces #N/A if all 12 entries are blank.
The first formula would do that but the OP said there were no empty cells. -- Biff Microsoft Excel MVP "Tyro" wrote in message et... You formula produces #N/A if all 12 entries are blank. He's referring to the description of #N/A "T. Valko" wrote in message ... "A value is not available to the formula or function". Hmmm... I've never seen that error before so I really don't know what it means. the double quotation marks inside the final parentheses. What do they mean? They are used to return a blank *until* a number 0 is entered in the range. If you want some other result just replace the "" with whatever you want. Just remember that if you want some TEXT value returned to enclose the TEXT string in quotes like this: =IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"no data") If you want a 0 returned then you don't need the quotes: =IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),0) I've slightly changed the formula to: =IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"") Here's a very small sample file that demonstrates this: lastnum.xls 14kb http://cjoint.com/?bghmSfGDPm Enter some numbers (0) replacing the 0s and you'll see that it works. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Thank you all for your responses. FSt1 Entries in cells will not change HTH Your formula returns the error "A value is not available to the formula or function". Tyro The formula works very well . Thanks. If you have the time, and inclination, would you post a word description of the formula - I cannot follow it entirely. Biff The three answers are 'no', but your formula returns the same error "A value is not available to the formula or function". I tried to follow it through but stumbled on the double quotation marks inside the final parentheses. What do they mean? Should they have something between them? Regards, Ken |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help Please
Apparently there are blanks. :)
"T. Valko" wrote in message ... You formula produces #N/A if all 12 entries are blank. The first formula would do that but the OP said there were no empty cells. -- Biff Microsoft Excel MVP "Tyro" wrote in message et... You formula produces #N/A if all 12 entries are blank. He's referring to the description of #N/A "T. Valko" wrote in message ... "A value is not available to the formula or function". Hmmm... I've never seen that error before so I really don't know what it means. the double quotation marks inside the final parentheses. What do they mean? They are used to return a blank *until* a number 0 is entered in the range. If you want some other result just replace the "" with whatever you want. Just remember that if you want some TEXT value returned to enclose the TEXT string in quotes like this: =IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"no data") If you want a 0 returned then you don't need the quotes: =IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),0) I've slightly changed the formula to: =IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"") Here's a very small sample file that demonstrates this: lastnum.xls 14kb http://cjoint.com/?bghmSfGDPm Enter some numbers (0) replacing the 0s and you'll see that it works. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Thank you all for your responses. FSt1 Entries in cells will not change HTH Your formula returns the error "A value is not available to the formula or function". Tyro The formula works very well . Thanks. If you have the time, and inclination, would you post a word description of the formula - I cannot follow it entirely. Biff The three answers are 'no', but your formula returns the same error "A value is not available to the formula or function". I tried to follow it through but stumbled on the double quotation marks inside the final parentheses. What do they mean? Should they have something between them? Regards, Ken |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help Please
How can there be blanks (I'm assuming you mean formula blanks "") if:
A row of 12 cells representing 12 months, all of which have an initial value of zero. I'm pretty sure this is overkill for the task at-hand but I just went through this same scenario with another person: =IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10)/(ISNUMBER(A1:L1)),A1:L1),"") That takes care of *EVERYTHING* : empty cells text 0s formula blanks errors booleans the kitchen sink! <vbg -- Biff Microsoft Excel MVP "Tyro" wrote in message . net... Apparently there are blanks. :) "T. Valko" wrote in message ... You formula produces #N/A if all 12 entries are blank. The first formula would do that but the OP said there were no empty cells. -- Biff Microsoft Excel MVP "Tyro" wrote in message et... You formula produces #N/A if all 12 entries are blank. He's referring to the description of #N/A "T. Valko" wrote in message ... "A value is not available to the formula or function". Hmmm... I've never seen that error before so I really don't know what it means. the double quotation marks inside the final parentheses. What do they mean? They are used to return a blank *until* a number 0 is entered in the range. If you want some other result just replace the "" with whatever you want. Just remember that if you want some TEXT value returned to enclose the TEXT string in quotes like this: =IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"no data") If you want a 0 returned then you don't need the quotes: =IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),0) I've slightly changed the formula to: =IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"") Here's a very small sample file that demonstrates this: lastnum.xls 14kb http://cjoint.com/?bghmSfGDPm Enter some numbers (0) replacing the 0s and you'll see that it works. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Thank you all for your responses. FSt1 Entries in cells will not change HTH Your formula returns the error "A value is not available to the formula or function". Tyro The formula works very well . Thanks. If you have the time, and inclination, would you post a word description of the formula - I cannot follow it entirely. Biff The three answers are 'no', but your formula returns the same error "A value is not available to the formula or function". I tried to follow it through but stumbled on the double quotation marks inside the final parentheses. What do they mean? Should they have something between them? Regards, Ken |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help Please
Around here, I'm sure the OPs get as much entertainment as education!<bg
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... How can there be blanks (I'm assuming you mean formula blanks "") if: A row of 12 cells representing 12 months, all of which have an initial value of zero. I'm pretty sure this is overkill for the task at-hand but I just went through this same scenario with another person: =IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10)/(ISNUMBER(A1:L1)),A1:L1),"") That takes care of *EVERYTHING* : empty cells text 0s formula blanks errors booleans the kitchen sink! <vbg -- Biff Microsoft Excel MVP "Tyro" wrote in message . net... Apparently there are blanks. :) "T. Valko" wrote in message ... You formula produces #N/A if all 12 entries are blank. The first formula would do that but the OP said there were no empty cells. -- Biff Microsoft Excel MVP "Tyro" wrote in message et... You formula produces #N/A if all 12 entries are blank. He's referring to the description of #N/A "T. Valko" wrote in message ... "A value is not available to the formula or function". Hmmm... I've never seen that error before so I really don't know what it means. the double quotation marks inside the final parentheses. What do they mean? They are used to return a blank *until* a number 0 is entered in the range. If you want some other result just replace the "" with whatever you want. Just remember that if you want some TEXT value returned to enclose the TEXT string in quotes like this: =IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"no data") If you want a 0 returned then you don't need the quotes: =IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),0) I've slightly changed the formula to: =IF(COUNTIF(A1:L1,"0"),LOOKUP(2,1/(A1:L10),A1:L1),"") Here's a very small sample file that demonstrates this: lastnum.xls 14kb http://cjoint.com/?bghmSfGDPm Enter some numbers (0) replacing the 0s and you'll see that it works. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Thank you all for your responses. FSt1 Entries in cells will not change HTH Your formula returns the error "A value is not available to the formula or function". Tyro The formula works very well . Thanks. If you have the time, and inclination, would you post a word description of the formula - I cannot follow it entirely. Biff The three answers are 'no', but your formula returns the same error "A value is not available to the formula or function". I tried to follow it through but stumbled on the double quotation marks inside the final parentheses. What do they mean? Should they have something between them? Regards, Ken |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|