+ Reply to Thread
Results 1 to 8 of 8

cell not display result even formula result indicate result is correct

  1. #1
    Registered User
    Join Date
    11-26-2015
    Location
    singapore
    MS-Off Ver
    2010
    Posts
    3

    cell not display result even formula result indicate result is correct

    hi,
    i need help on excel, I already written a visual basic code to download the data. However i need to convert the raw data into a table format.
    on the left are the raw data downloaded and i need to convert them into the table on the right
    Capture.JPG
    pls see the image below, for cell G5, the formula result indicate i get the formula correct, it is pointing to B16 but the result will not be indicate in the cell G5Capture1.JPG

    i have attached my excel sheet
    http://s000.tinyupload.com/?file_id=...21399228990576
    Last edited by 7ryota; 11-26-2015 at 10:57 AM. Reason: attachment

  2. #2
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: cell not display result even formula result indicate result is correct

    Please upload the file directly to the forum as many will not download from file services.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  3. #3
    Registered User
    Join Date
    11-26-2015
    Location
    singapore
    MS-Off Ver
    2010
    Posts
    3

    Re: cell not display result even formula result indicate result is correct

    hi,
    as requested,
    i got 2 sheet with similar problem
    in the monthly data sheet, for year 1996, i cant get the result to show.
    I have 2 formula in the table
    Attached Files Attached Files

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: cell not display result even formula result indicate result is correct

    I think that I have what you are looking for. I changed the formula in F4 to the following and filled across and down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Attached Files Attached Files

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: cell not display result even formula result indicate result is correct

    I forgot the second worksheet.
    Formula for Monthly Data G4 filled across and down is
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

  6. #6
    Registered User
    Join Date
    11-26-2015
    Location
    singapore
    MS-Off Ver
    2010
    Posts
    3

    Re: cell not display result even formula result indicate result is correct

    thanks man, can u explain what the formula does?
    u just solve my problem

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    5,772

    Re: cell not display result even formula result indicate result is correct

    A non-array formula option:

    =IFERROR(LOOKUP(2,1/(($C$5:$C$1060=$E4)*($D$5:$D$1060=G$3)),$B$5:$B$1060),"")

    Enter only

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: cell not display result even formula result indicate result is correct

    =IFERROR(INDEX($B$5:$B$1060,MATCH($E4&F$3,$C$5:$C$1060&$D$5:$D$1060,0),0),"")
    IFERROR - This function returns a value (which is at the end of the formula) if the formula returns an error value. In this case it is "" or a null value for the cell.
    INDEX - This function identifies where the data is that you want to retrieve. In this formula the values are in column B. INDEX has 3 arguments. The first is the ARRAY or range of cells, the second is the ROW where the data wanted is to be found and the third argument is the COLUMN where the data is to be found. In this formula we know that the data to be retrieved is in one column and it has been identified by the INDEX ARRAY argument so a column argument will not be required.
    MATCH - has 3 arguments: A Value, an ARRAY and the type of match. usually the match is a simple match of a value within a range. However here we have the concatenation of the values in E4 and F3 to be found in the combination of the columns E and F. The type of match is indicated by the 0 which corresponds to FALSE (exact). MATCH can also have a value of +1 or -1. +1 or omitted finds the largest value that is less than or equal to the lookup value. -1 finds the smallest value that is greater than or equal to the lookup value. We want an exact match so the 0 is used.
    The last 0 in the formula can be omitted. It is the column number and we already know that there is only 1 column and it has been identified so the 0 is really unnecessary.

    The formula is an array formula because of the combining of elements to be evaluated against another combination of elements.

    bebi921999 has a non array formula and perhaps could supply you an explanation of the LOOKUP formula provided.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Code result wrong but formula correct result
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-11-2014, 12:26 PM
  2. Formula result correct, but shows up incorrectly in cell
    By KatieXM in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-04-2012, 02:34 PM
  3. Replies: 3
    Last Post: 11-24-2011, 06:18 AM
  4. Disable Solver Result Dialog Box & Display Result in Cell
    By yauchildchew in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-04-2010, 01:46 PM
  5. Excel 2007 : copied formula doesn't display correct result
    By frustratedinWA in forum Excel General
    Replies: 2
    Last Post: 03-05-2009, 02:56 PM
  6. [SOLVED] Advanced formula - Return result & Show Cell Reference of result
    By Irv in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-05-2006, 10:40 PM
  7. copied formula has correct cell reference, but result of original
    By lvito in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-14-2005, 12:05 PM

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