Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell reference within array
I am trying to use a cell reference within an array, but Excel won't
let me. In my case, here is the formula I want: =IRR(A8,50,50,50,50) A8 is a negative number that will change by row: the next line would read =IRR(A9,50,50,50,50) Ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell reference within array
On May 4, 3:53*pm, wrote:
I am trying to use a cell reference within an array, but Excel won't let me. In my case, here is the formula I want: =IRR(A8,50,50,50,50) A8 is a negative number that will change by row: the next line would read =IRR(A9,50,50,50,50) Ideas? Nothing? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell reference within array
Why not share the real formula and describe what you're doing and how it fails?
wrote: On May 4, 3:53 pm, wrote: I am trying to use a cell reference within an array, but Excel won't let me. In my case, here is the formula I want: =IRR(A8,50,50,50,50) A8 is a negative number that will change by row: the next line would read =IRR(A9,50,50,50,50) Ideas? Nothing? -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell reference within array
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell reference within array
Glenn wrote:
wrote: On May 4, 3:53 pm, wrote: I am trying to use a cell reference within an array, but Excel won't let me. In my case, here is the formula I want: =IRR(A8,50,50,50,50) A8 is a negative number that will change by row: the next line would read =IRR(A9,50,50,50,50) Ideas? Nothing? Put 50 in B8:E8 (you can hide those columns) and use this: =IRR(A8:E8) Or, put 50 in cell A1. Select cell A8, then Insert / Name / Define "ARRAY" as follows: =Sheet1!$A8,Sheet1!$A$1,Sheet1!$A$1,Sheet1!$A$1,Sh eet1!$A$1 Then use this: =IRR(ARRAY) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell reference within array
On May 5, 12:23*pm, Glenn wrote:
wrote: On May 4, 3:53 pm, wrote: I am trying to use a cell reference within an array, but Excel won't let me. In my case, here is the formula I want: =IRR(A8,50,50,50,50) A8 is a negative number that will change by row: the next line would read =IRR(A9,50,50,50,50) Ideas? Nothing? Put 50 in B8:E8 (you can hide those columns) and use this: =IRR(A8:E8) Glenn, Thanks, That is indeed what I ended up doing and it solved the problem. I was looking for a more elegant solution, though. Dave, that was a weird comment. This is the real formula. Maybe I forgot the brackets, but basically cell A8 would change and the formula would ideally read =IRR({$A8,50,50,50,50},0.15) Any more thoughts are welcome. Thanks again. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell reference within array
On May 5, 5:31*pm, Dave Peterson wrote:
I pasted your original formula and the new formula into the formulabar and xl2003 yelled at me saying that each contained an error. What formula worked for you? What cell did you place the formula? wrote: On May 5, 12:23 pm, Glenn wrote: wrote: On May 4, 3:53 pm, wrote: I am trying to use a cell reference within an array, but Excel won't let me. In my case, here is the formula I want: =IRR(A8,50,50,50,50) A8 is a negative number that will change by row: the next line would read =IRR(A9,50,50,50,50) Ideas? Nothing? Put 50 in B8:E8 (you can hide those columns) and use this: =IRR(A8:E8) Glenn, Thanks, That is indeed what I ended up doing and it solved the problem. I was looking for a more elegant solution, though. Dave, that was a weird comment. This is the real formula. Maybe I forgot the brackets, but basically cell A8 would change and the formula would ideally read =IRR({$A8,50,50,50,50},0.15) Any more thoughts are welcome. Thanks again. -- Dave Peterson The formula that would work is =IRR({-40,50,50,50,50},0.15) The only difference is that I want -40 to be A8. The formula may change cells, but is is on C8 for now. I don't think that matters, though |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell reference within array
wrote...
.... I am trying to use a cell reference within an array, but Excel won't let me. In my case, here is the formula I want: =IRR(A8,50,50,50,50) .... Excel prohibits this because IRR takes a single argument, but you're trying to pass 5 arguments. IRR accepts array first arguments, so make this an array. =IRR(IF({1;0;0;0;0},A8,50)) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell reference within array
Harlan Grove wrote:
wrote... ... I am trying to use a cell reference within an array, but Excel won't let me. In my case, here is the formula I want: =IRR(A8,50,50,50,50) ... Excel prohibits this because IRR takes a single argument, but you're trying to pass 5 arguments. IRR accepts array first arguments, so make this an array. =IRR(IF({1;0;0;0;0},A8,50)) Nice! |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell reference within array
One more option (similar to Glenn's without the name) if B8=50:
=IRR((A8,B8,B8,B8,B8)) [Maybe of interest: if({1},A1:A2) is different from if(1,A1:A2), the first converts the reference to an array e.g. try N() on both.] " wrote: I am trying to use a cell reference within an array, but Excel won't let me. In my case, here is the formula I want: =IRR(A8,50,50,50,50) A8 is a negative number that will change by row: the next line would read =IRR(A9,50,50,50,50) Ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup - using a cell to reference a named array | Excel Worksheet Functions | |||
Replacing a Table-array with a cell reference in vlookup | Excel Worksheet Functions | |||
Returning the cell reference when you find a value in an array | Excel Worksheet Functions | |||
use cell reference,whose contents= a table array name for Vlookup | Excel Worksheet Functions | |||
How can I use a cell reference in Sumproduct array formula? | Excel Discussion (Misc queries) |