+ Reply to Thread
Results 1 to 6 of 6

IF + If-And formula not calculating correctly

  1. #1
    Registered User
    Join Date
    06-11-2018
    Location
    Sharjah, United Arab Emirates
    MS-Off Ver
    2016
    Posts
    2

    Question IF + If-And formula not calculating correctly

    Dear All,

    I have a formula as per below:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    =IFERROR(IF(D3=0,0,IF(D3>2500,D3-(D3*$G$3%),IF(AND(D3<=2500,D3-(D3*$H$3%)<=100),D3-$F$3,D3-(D3*$H$3%)))),0)

    B3 = net amount. This cell has the formula
    D3 = Cost
    F3 = 100.00
    G3 = 10 (10%)
    H3 = 25 (25%)



    A) if cost is more than 2500 then net amount should be 2500-10%

    B) if cost is less than or equal to 2500 then net amount should be 2500-25%

    C) if cost is a) less than or equal to 2500 & b) cost - 25% is less than or equal to 100 then net amount should be cost-100

    The formula seems to work fine for Point A and B.

    For point C the formula seems to work fine. If the cost is 100 then net amount shows as 0.00, if the cost is 50 then net amount shows as -50.00. For cost between 100 to 133 the net amount shows the cost-100, however for some reason this only works fine for cost from 100 to 133 and if the cost is 134 and more, the formula shows the result as per point B

    I will be thankful if someone can guide me to correct/amend the formula and explain the reason of this mis-calculation for point C.

    With kind regards,

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: IF + If-And formula not calculating correctly

    perhaps
    =IFERROR(IF(D3=0,0,IF(D3>2500,D3-(D3*$G$3%),IF(D3-(D3*$H$3%)<=100,-100,D3-(D3*$H$3%)))),0)

    you do not need the and statement as the <=2500 bit has to be true

  3. #3
    Registered User
    Join Date
    06-11-2018
    Location
    Sharjah, United Arab Emirates
    MS-Off Ver
    2016
    Posts
    2

    Re: IF + If-And formula not calculating correctly

    Quote Originally Posted by davsth View Post
    perhaps
    =IFERROR(IF(D3=0,0,IF(D3>2500,D3-(D3*$G$3%),IF(D3-(D3*$H$3%)<=100,-100,D3-(D3*$H$3%)))),0)

    you do not need the and statement as the <=2500 bit has to be true
    i tried your suggestion but unfortunately it does work.

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: IF + If-And formula not calculating correctly

    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 then scroll down to Manage Attachments to open the upload window.

  5. #5
    Valued Forum Contributor jtakw's Avatar
    Join Date
    05-05-2018
    Location
    CA, USA
    MS-Off Ver
    2016
    Posts
    668

    Re: IF + If-And formula not calculating correctly

    Hi,

    Your formula is actually doing Exactly what your description says and is producing the correct results.

    Take 133 as the Cost for example: Cost - 25% = 133-133*.25 = 133-33.25 = 99.75 (result is less than 100), so your formula gives you Cost - 100 = 133-100 = 33 This is correct according to your requirements (point C)
    Take 134 as the Cost for example: Cost - 25% = 134-134*.25 = 134-33.50 = 100.5 (result is more than 100), so your formula gives you Cost - 25% = 134-134*.25 = 100.5 This is correct according to your requirements (point B)

    If this is Not what you want as the result, then you need to re-think your requirements and logic to re-built the formula.

    =IFERROR(IF(D3=0,0,IF(D3>2500,D3-D3*$G$3%,IF(D3-D3*$H$3%<=100,D3-$F$3,D3-D3*$H$3%))),0)

  6. #6
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: IF + If-And formula not calculating correctly

    my solution: If(excel example.xlsx=true, "Fast and good results tailored to your needs", "lots of if's, but's then's and still no result")
    Last edited by Roel Jongman; 06-12-2018 at 10:11 AM.

+ 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. Formula not calculating correctly?
    By Dooberry in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-20-2015, 06:38 AM
  2. [SOLVED] Formula not calculating correctly
    By ratboyab in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-29-2013, 06:35 PM
  3. Formula not calculating correctly
    By mgfuentes in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-09-2012, 01:21 PM
  4. Formula not calculating correctly
    By Whittle82 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2012, 05:54 PM
  5. Excel Not Calculating Formula Correctly
    By Njisom13 in forum Excel General
    Replies: 2
    Last Post: 12-27-2010, 07:38 AM
  6. Formula Not Calculating Correctly
    By be965 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-23-2007, 03:30 PM
  7. Replies: 10
    Last Post: 09-01-2006, 05:47 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