+ Reply to Thread
Results 1 to 5 of 5

Problem with Excel formula returning #VALUE! or Incorrect result

  1. #1
    Forum Contributor
    Join Date
    05-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    120

    Problem with Excel formula returning #VALUE! or Incorrect result

    Dear All,

    I should be grateful if you would, please, help me with Excel formula that is returning either #VALUE! in a ‘Total’ cell or incorrect addition/subtraction result when any one of the cells is blank.

    I have attached a sample file showing an example of what I am doing, with an explanation below of the difficulty that I am having, and for which I need your kind help.

    1. From the Sample file, in Cell P90, I have the following formula =N86 which returns a value.
    2. In Cell P91, I also have the following formula which returns a value or the cell is blank if there is no value to return:

    ‘=IF(SUMPRODUCT(--(AM6:AM85=AH86),E6:E85)=0,"",SUMPRODUCT(--(AM6:AM85=AH86),E6:E85))

    3. For Cell P92, which gives the result of subtracting cell P91 from cell P90, I have the following formula: ‘=IFERROR(P90-P92,"")

    4. When there are numbers in both P90 and P91, cell P92 returns a correct balance.

    5. However, the following problems arise with the formula in cell P92 (the 'Total' cell):

    a) If Cell P90 is blank but with a number in Cell P91 e.g. £300, Cell P92 becomes blank. Under this circumstance, I would expect P92 to show “-£300” i.e. minus £300.

    b) Equally, if there is a number in Cell P90, e.g. £500 but with P91 being blank, again, P92 becomes blank instead of £500 in this circumstance (£500 minus zero) should be £500 and not zero!.

    When I tried altering the formula in Cell P92, I get #VALUE!, and when I use the formula shown, I get incorrect answers. I must be doing something wrong wrong with the formula in Cell P92.

    I need help, please, with the correct formula for Cell P92 to cater for the two scenarios in (a) and (b) above.

    Thanks all for your help.

    Buddy8
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,117

    Re: Problem with Excel formula returning #VALUE! or Incorrect result

    That workbook has no formulas or sample data...
    Ben Van Johnson

  3. #3
    Forum Contributor
    Join Date
    05-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    120

    Re: Problem with Excel formula returning #VALUE! or Incorrect result

    Hi ProtonLeah,

    Thanks for your kind response.

    Yes, the workbook has no formula. I showed, on the right side of each cell, the formula that I have in the main workbook, and explained the problem that I have using such formula. I am hoping that someone would be able to examine the formulas that I have shown beside each cell to see what I am doing wrong hence Cell P92 is giving the wrong answers with respect to scenarios (a) and (b).

    The reason that I have not included the actual workbook with the formulas is because Cell P91, as you can see, is a SUMPRODUCT formula which pulls together numbers from various cells to produce a single value. However, if I must provide the actual workbook with all the numbers making up the value in Cell P91, please let me know and I'll try and do that. I thought the way I presented it would help.

    The problem really is that, with those formulas shown in each cell, I don't know why if there is a number in Cell P90 and P91 is blank, the formula in P92 returns a wrong answer. Also, if P90 is blank and there is a number in Cell P91, the formula in P92 again returns a wrong answer. But if there are numbers in both P90 and P91, the formula in P92 returns a correct answer! This is my dilemma with which I need help.

    I hope you can, please, help with this.

    Thank you.
    .

    Buddy8

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,084

    Re: Problem with Excel formula returning #VALUE! or Incorrect result

    Quote Originally Posted by Buddy8 View Post
    3. For Cell P92, which gives the result of subtracting cell P91 from cell P90, I have the following formula: ‘=IFERROR(P90-P92,"")
    If one may assume that your formula is in fact =IFERROR(P90-P91,"") then you might simply amend the formula to =IFERROR(P90-N(P91),"")
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    25,376

    Re: Problem with Excel formula returning #VALUE! or Incorrect result

    When I use your formulae, it seems fine. So, you must be doing something odd.

    Will you please attach a sample Excel workbook? Please don't attach an empty file. That really doesn't help at all!! Show us your formulae, in place.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



+ 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] Excel 2010 - SUM formula returns incorrect result
    By digita in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-15-2016, 01:06 AM
  2. isblank & vlookup formula returns #n/a result and incorrect result
    By helpmeplease333 in forum Excel General
    Replies: 5
    Last Post: 05-06-2012, 11:41 PM
  3. VLookup formula returning an incorrect result
    By Spiritseeker in forum Excel General
    Replies: 2
    Last Post: 03-23-2010, 07:51 AM
  4. Vlookup returning incorrect result?
    By Delarado in forum Excel General
    Replies: 9
    Last Post: 08-19-2009, 07:22 AM
  5. Index formula returning incorrect result
    By Nadir Soofi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-18-2008, 02:08 PM
  6. IF function returning incorrect result
    By Hillary E. in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-22-2006, 12:43 PM
  7. Incorrect syntax near '#' problem when returning to MS Query fromExcel
    By Steen Persson (DK) in forum Excel General
    Replies: 0
    Last Post: 03-07-2006, 11:30 AM

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