+ Reply to Thread
Results 1 to 13 of 13

Request help in correcting IF Statement which should not return negative value

  1. #1
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    143

    Request help in correcting IF Statement which should not return negative value

    Dear Experts

    Ref to attached sheet

    1. The formulas in columns AD, AE and AF are supposed to work in such a way that column AG does not result in negative value.

    2. However, if we notice column AG it is resulting in negative.

    3.But, if I update Column Y with sum(S:X) it is not resulting in negative and working correctly. Some minor mistake in formulas in AD, AE and AF which I am unable to find out.

    Request to please help
    Attached Files Attached Files
    Last edited by grcshekar; 04-17-2020 at 03:23 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,146

    Re: Request help in correcting IF Statement

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,945

    Re: Request help in correcting IF Statement which should not return negative value

    Depending on what you really want, which isn't completely clear:

    =ABS(ROUND(SUM(S2:X2)-SUM(Y2:AF2),0))

    or:

    =MAX(0,(ROUND(SUM(S2:X2)-SUM(Y2:AF2),0)))
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  4. #4
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    143

    Re: Request help in correcting IF Statement which should not return negative value

    HI,

    Columns AD, AE and AF are formula fields which has calculation. This calculation should display results based on SUM(S:X) - SUM(Y........ONE CELL BEFORE CALCULATION FIELD)

    Example: Column AD. If the Calculated field has value say 200 then if SUM(S:X) - Sum(Y-AC) is >=200 only then Colmun AD should display 200 else 0

    Similarly say if Column AE has value of 1800 (this is also by formula) then if SUM(S:X) - Sum(Y-AD) is >=1800 only then Colmun AE should display 200 else 0

    Similary for AF

    For this, I need to modify existing forumla from AD to AF. There is some minor error because it works in certain cases and not in other case
    Last edited by AliGW; 04-17-2020 at 08:05 AM. Reason: Please don't quote unnecessarily!

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,146

    Re: Request help in correcting IF Statement which should not return negative value

    Administrative Note:

    Please don't quote entire posts unnecessarily. They clutter threads and make them hard to read.
    Use the "Quick reply" instead
    Thanks

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,945

    Re: Request help in correcting IF Statement which should not return negative value

    In order to help us, please provide a workbook that shows what you want. Add an extra column and enter (manually) the results you wish to see.

    Please don't open any further threads on this issue - each thread you open is just a slight tweak on the previous request, and you will keep having to explain yourself over and over if you do this. ALL further issues with the formula in this workbook need to be addressed here in this thread, please.

  7. #7
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    143

    Re: Request help in correcting IF Statement which should not return negative value

    Ok. I do understand. Now I wrote a formula like this

    =IF((AND(SUM(S2:X2)<=15000, SUM(S2:X2)-SUM(Y2:AD2)>=SUM(S2:X2)*12%)), SUM(S2:X2)*12%, 0). This works the way i need it to. Now let us see this formula =IF((AND(SUM(S2:X2)>15000, SUM(S2:X2)-SUM(Y2:AD2)>=15000*12%)), 15000*12%, 0). Now I need to combine these two formula into one to make it work on both the conditions which ever is met subject to IF(INDEX(EmployeeMaster!O:O,MATCH(H2,EmployeeMaster!A:A,0))="Y",

  8. #8
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    143

    Re: Request help in correcting IF Statement which should not return negative value

    I resolved. This is the correct formula


    =IF(INDEX(EmployeeMaster!O:O,MATCH(H6,EmployeeMaster!A:A,0))="Y",IF((AND(SUM(S6:X6)<=15000,
    SUM(S6:X6)-SUM(Y6:AD6)>=SUM(S6:X6)*12%)), SUM(S6:X6)*12%,IF((AND(SUM(S6:X6)>15000, SUM(S6:X6)-SUM(Y6:AD6)>=15000*12%)), 15000*12%,0)))

  9. #9
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    143

    Re: Request help in correcting IF Statement which should not return negative value

    Finally, ref to worksheet attached, below are formulas which is working correctly for the benefit of other forum users.


    AD2 = IF(AND(SUM(S2:X2)>=Parameters!$B$9,SUM(S2:X2)-SUM(Y2:AC2)>=Parameters!$B$10),Parameters!$B$10,0)

    AE2 = =IF(INDEX(EmployeeMaster!O:O,MATCH(H2,EmployeeMaster!A:A,0))="Y",IF((AND(SUM(S2:X2)<=Parameters!$B$5,
    SUM(S2:X2)-SUM(Y2:AD2)>=SUM(S2:X2)*Parameters!$B$6%)), SUM(S2:X2)*Parameters!$B$6%,IF((AND(SUM(S2:X2)>Parameters!$B$5, SUM(S2:X2)-SUM(Y2:AD2)>=Parameters!$B$5*Parameters!$B$6%)), Parameters!$B$5*Parameters!$B$6%,0)))

    AF2 = IF((AND(SUM(S2:X2)<=Parameters!$B$7, SUM(S2:X2)-SUM(Y2:AE2)>=SUM(S2:X2)*Parameters!$B$8%)), SUM(S2:X2)*Parameters!$B$8%, 0)

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,945

    Re: Request help in correcting IF Statement which should not return negative value

    Thank you.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  11. #11
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    143

    Re: Request help in correcting IF Statement which should not return negative value

    I still have an error.

    =IF(INDEX(EmployeeMaster!O:O,MATCH(H2,EmployeeMaster!A:A,0))="Y",IF((AND(SUM(S2:X2)<=Parameters!$B$5,
    SUM(S2:X2)-SUM(Y2:AD2)>=SUM(S2:X2)*Parameters!$B$6%)), SUM(S2:X2)*Parameters!$B$6%,IF((AND(SUM(S2:X2)>Parameters!$B$5, SUM(S2:X2)-SUM(Y2:AD2)>=Parameters!$B$5*Parameters!$B$6%)), Parameters!$B$5*Parameters!$B$6%,0)))

    If EmployeeMaster!O:O,MATCH(H2,EmployeeMaster!A:A,0))="Y". If this is "N" it is displaying at FALSE instead of 0

  12. #12
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    143

    Re: Request help in correcting IF Statement which should not return negative value

    For AE

    AE==IF(INDEX(EmployeeMaster!O:O,MATCH(H2,EmployeeMaster!A:A,0))="Y",IF((AND(SUM(S2:X2)<=Parameters!$B$5,
    SUM(S2:X2)-SUM(Y2:AD2)>=SUM(S2:X2)*Parameters!$B$6%)), SUM(S2:X2)*Parameters!$B$6%,IF((AND(SUM(S2:X2)>Parameters!$B$5, SUM(S2:X2)-SUM(Y2:AD2)>=Parameters!$B$5*Parameters!$B$6%)), Parameters!$B$5*Parameters!$B$6%,0)),0)

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,945

    Re: Request help in correcting IF Statement which should not return negative value

    OK - if you want more help, add an updated workbook.

+ 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] Find if a request was opened twice or more using duplicates and request date
    By wellous in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-22-2020, 05:28 AM
  2. Replies: 0
    Last Post: 01-11-2017, 11:08 AM
  3. Wait for winhttp request to be done (much like XML http request)
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 07-23-2015, 09:26 AM
  4. Replies: 15
    Last Post: 06-12-2011, 11:05 PM
  5. Request help with Case Statement and Merged Cells
    By kys2000 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-05-2007, 06:18 PM
  6. [SOLVED] Correcting a #REF!
    By David Hauck in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-20-2005, 01:20 PM

Tags for this Thread

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