+ Reply to Thread
Results 1 to 26 of 26

Formula returns wrong data type

  1. #1
    Registered User
    Join Date
    01-29-2010
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Formula returns wrong data type

    I encounter an intriguing problem with named formula in Excel 2007, it seems to be a bug, I am able to produce a rather simply file attached here to illustrate the bug, but I cannot figure out what triggers it.


    On my "Sheet2", cell A6:I10 contains a formula
    =TYPE(INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN())))
    This basically tell me what are in the corresponding cells on Sheet1.
    It returns correct types: 2 for text and 1 for empty cell.

    The problem is I defined a named formula called "test", please check under menu
    Formulas > Name Manager.

    You will see I have a global name "test" alias to
    =TYPE(INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN())))

    Now on Sheet2, cell A1:I5 contains "=test", which suppose to return 2 and 1s.
    But instead it return array type "64"!

    Somehow the named formula sees different data type compared to the formula itself?

    Thanks in advance!
    Attached Files Attached Files

  2. #2
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Formula returns wrong data type

    Hi mbrown89
    64 is an array

    the formula in the name list has an array value
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  3. #3
    Registered User
    Join Date
    01-29-2010
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Formula returns wrong data type

    Sure, I know 64 represents an array, which is the exact bug.
    The formula should return 2, because the corresponding cell value is a text "O".

    The exact same formula returns 2 when I use it explicitly (say in row 6).
    But when I give the formula a name "test", and then use the named formula (say in row 1), it returns 64, which is incorrect.

  4. #4
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Formula returns wrong data type

    I see that the text is =TYPE(INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN()))) which is an array

    INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN()))) = array

  5. #5
    Registered User
    Join Date
    01-29-2010
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Formula returns wrong data type

    "Sheet1"&Address(Row(), Column())
    gives an address of a single cell, not an array.

    Please check out the Cell A6, which uses the exact formula, you won't suggest Cell A6 should return "64", right? Hope you see the problem I try to explain here.

  6. #6
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Formula returns wrong data type

    Hi mbrown89

    "Test" is the array

  7. #7
    Registered User
    Join Date
    01-29-2010
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Formula returns wrong data type

    I don't understand you. Could you check out Cell A6 and explain me why that cell returns 2? According to your explanation, A6 should return 64 as well.

  8. #8
    Registered User
    Join Date
    01-29-2010
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Formula returns wrong data type

    If you define "test" as

    =TYPE(INDIRECT("Sheet1!"&ADDRESS(1,1)))

    the cell will return "2" correctly.

    If you define test as
    =TYPE(INDIRECT("Sheet1!"&ADDRESS(ROW(),1)))

    it now returns "64", which is a bug.

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formula returns wrong data type

    In essence it's down to your use of ROW() & COLUMN()

    eg:

    Please Login or Register  to view this content.
    you will find the values will be returned correctly.
    Last edited by DonkeyOte; 01-30-2010 at 06:37 AM.

  10. #10
    Registered User
    Join Date
    01-29-2010
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Formula returns wrong data type

    I need ROW(), COLUMN() for good reasons. This is just a simplified example to illustrate the bug, not the exact project I try to solve.

    Do you think it's a bug or can you offer a good explanation that it should return 64?

  11. #11
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Formula returns wrong data type

    Hi mbrown89
    The Defined named text can be for a cell or a range of cell
    In Test Row is a range
    try it with row(A1), column(A1)

  12. #12
    Registered User
    Join Date
    01-29-2010
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Formula returns wrong data type

    =TYPE(INDIRECT("sheet1!"&ADDRESS(ROW(Sheet2!A1),COLUMN(Sheet2!A1))))

    returns 64 as well.

  13. #13
    Registered User
    Join Date
    01-29-2010
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Formula returns wrong data type

    ROW() is the same as ROW(current cell)
    > If the reference parameter is omitted, then the Row function assumes that the reference is the cell address in which the Row function has been entered in.

  14. #14
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Formula returns wrong data type

    Didn't see donkeyOte reply
    there your have a definitive answer it to restrict the cells (Array) to a cell use
    text=TYPE(INDIRECT("Sheet1!"&ADDRESS(ROWS(A$1:A1),COLUMNS($A1:A1))))

  15. #15
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formula returns wrong data type

    Quote Originally Posted by mbrown89
    I need ROW(), COLUMN() for good reasons.
    Please elaborate... if you need to reference based on current cell position use RC referencing in the INDIRECT, eg:

    Please Login or Register  to view this content.
    Quote Originally Posted by mbrown
    Do you think it's a bug or can you offer a good explanation that it should return 64?
    Not sure I would say it's a bug... but equally I don't have the knowledge to explain it "properly".

    I would put it down to the fact that

    a) All Names formula are processed as Array functions (same holds true for Validation, Conditional Formatting etc)
    note: this does not mean all names would return output type of 64

    To illustrate, if we add:

    Please Login or Register  to view this content.
    We know that we to retrieve MAX of A in a cell we need to use an Array, eg:

    Please Login or Register  to view this content.
    Result, when confirmed as an Array, is 3 as expected.

    We can illustrate the Array nature of Name functions by creating the following Name:

    Please Login or Register  to view this content.
    Now in our cell we can replace the earlier CSE function with:

    Please Login or Register  to view this content.
    The result will be 3 ... ie the Name is processed as a CSE function by default.



    b) The ROW / COLUMN functions return value arrays - eg:

    Please Login or Register  to view this content.
    The return is 1, now, in the formula bar highlight the function and press F9 - you will see: {1;2;3;4;5;6;7;8;9;10} - ie an array of values

    Now change the formula reference range from A1:A10 to A1 (ie single cell) and repeat the above - you will see {1} - ie still an "array" of sorts

    Now change the formula from ROW(A1) to ROWS(A1) and repeat the above - you will see 1 - ie not an array return.



    So, conceded, you're using ROW()/COLUMN() outside of a Named Range without the 64 return but I would say that a) coupled with b) leads to Type 64 output.

    I am trying to think of other ways to demonstrate the same output using different functions but this early on a Sat morning I'm struggling....

  16. #16
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formula returns wrong data type

    Rather than add the below as an Edit I figured I should post again...

    Another way perhaps to demonstrate this... using native in-cell functions rather than names:

    Please Login or Register  to view this content.
    the latter will return 64.

    Please Login or Register  to view this content.
    will return the same output - ie the latter is not 64 return.

    So, sounding like a broken record, it is the use of functions that return arrays of values used in Array form that will generate type 64
    Using an Array function (CSE) in it's own right does not necessarily generate 64, nor does using functions that return arrays of values (eg ROW/COLUMN) -- rather it is the combination of the two factors that generate 64 return.

  17. #17
    Registered User
    Join Date
    01-29-2010
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Formula returns wrong data type

    Yes, DonkeyOte provides a nice trick ROWS(A$1:A1) to replace ROW()!
    Although I still believe this is an Excel bug in evaluating ROW within named formula, at least I now learn a way to avoid the bug.
    Thank you both!

  18. #18
    Registered User
    Join Date
    01-29-2010
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Formula returns wrong data type

    Thank you so much, DonkeyOte!
    I am satisfied with the explanation you provided.

  19. #19
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Formula returns wrong data type

    Hi mbrown89
    So any combination of functions that invoke an array will be 64
    what are you trying to do?

  20. #20
    Registered User
    Join Date
    01-29-2010
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Formula returns wrong data type

    What I initially trying to do is something like

    =IF( INDIRECT("Sheet1!"&ADDRESS(ROW()+3, COLUMN()-7))="X", Expression1, Expression2)

    It works well, but once I give it a name, it broke. I ended up doing
    =IF( INDEX( INDIRECT("Sheet1!"&ADDRESS(ROW()+3, COLUMN()-7)), 1)="X", Expression1, Expression2)

    But I am not happy with that, b/c why should I enter different formula just b/c I try to give it a name? So I post for help.

    BTW, how would I use the RC style address to accomplish this in named formula? (The cells in the Sheet2 needs to fetch corresponding cells in Sheet1 with an offset in both row and column)

  21. #21
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formula returns wrong data type

    I am not sure why you're using a name in truth and the use of INDIRECT obviously makes things volatile but we'll assume you're doing this because you need to (and we don't necessarily know all the ins and outs of the situation)

    Quote Originally Posted by mbrown89
    how would I use the RC style address to accomplish this in named formula?
    Perhaps:

    Please Login or Register  to view this content.
    For a basic overview of R1C1 notation you could see an old post of mine - I'm no expert in truth but I'd say that the link covers most of the basics: http://www.excelforum.com/2171545-post2.html

  22. #22
    Registered User
    Join Date
    01-29-2010
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    [SOLVED] Formula returns wrong data type

    Thanks.

    The main reasons for using named formula are three 1) my final formula are quite long, so I try to name some parts to make the final formula more readable. 2) this formula will be applied to lots of cells, but replacing the long formula with a name makes the file size much smaller. 3) if I need to modify the formula, I only need to change it in one place, rather than change the formula for one cell, then drag it and apply the new version to all the cells.

    Thanks again for all the great advice!

  23. #23
    Registered User
    Join Date
    01-29-2010
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Formula returns wrong data type

    My address offset is not a fixed constant, the value of the offset comes from another cell.
    Indirect("sheet1!"&address(row()+G12,column()+H12))

    Without Indirect(), I don't know how to achieve that.

  24. #24
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formula returns wrong data type

    EDIT - below relates to post #22 rather than #23 which was posted at the same time as this...


    OK - but as I say - the use of INDIRECT makes all of your functions Volatile - if as implied you have lots of them this is likely to slow down your file - ie generates calculation overheads.

    I would say as my own general rule of thumb use of INDIRECT is only really required if the target (ie sheet) is variable and/or the sheet referenced is physically removed & replaced (such that otherwise links would result in #REF! errors upon deletion)

    For more info. on Volatility - see link in my sig. to Charles Williams' site.

    I guess most of the nerds here like myself would general forego succinct formulae for the sake of overall efficiency / performance... too often elegance <> efficiency in Excel (unfortunately).

  25. #25
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formula returns wrong data type

    Quote Originally Posted by mbrown89 View Post
    My address offset is not a fixed constant, the value of the offset comes from another cell.
    Indirect("sheet1!"&address(row()+G12,column()+H12))

    Without Indirect(), I don't know how to achieve that.
    Following on from prior post -- unless Sheet1 is a variable (changes) I would use INDEX

    For the sake of demo. let's assume active cell on Sheet2 is C1000 and that additonal offset values are stored in G12/H12 and they are constants for all cells:

    Please Login or Register  to view this content.
    does that work for you ?
    (in 2007 you can adjust the range reference obviously to incorporate the necessary dimensions of your overall grid)

  26. #26
    Registered User
    Join Date
    01-29-2010
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Formula returns wrong data type

    Your last post works for me. Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1