+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 17

Syntax in formula giving incorrect result

  1. #1
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    285

    Syntax in formula giving incorrect result

    Hi,

    Total stuck in the formula in J14 , any help on this will be highly appreciated.

    J14 should give result 13 instead of 14. No idea why it gives result 14, please see the attach image & sample excel sheet.

    Please Login or Register  to view this content.
    error.JPG

    Salary Emp.xlsx

    Thank you.

    Best,
    Last edited by Glenn Kennedy; 08-14-2019 at 03:34 AM.

  2. #2
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,314

    Re: Stuck in the Subtraction formula :(

    Use the Evaluate Formula to step through the evaluation and spot your mistake in logic. I think you want:

    =IF(E14<15, E14, E14-F14+AND(F14>0,G14="Yes")+H14-I14)

    Also note the change in J16: 19 instead of 20. I think that's correct.

  3. #3
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    285

    Re: Stuck in the Subtraction formula :(

    Hi Joeu,

    Thank you for quick help, i spend more then 3 hours to figure it out the solution but couldn't find it
    Your formula gives the required result, but now it not counting 'G14="Yes"'.

    Summary of my salary sheet:
    1. If Employee has 'One Leaves Allowed' in G14 employee get one day extra wages. But this should not be True if present days are Zero in E14. i.e if present days are Zero in E14 and employee has 'One Leaves Allowed' G14, the Salary Paid in AG14 should be zero, as because present days are Zero in E14.

    2. If present days are not zero and Earned day is <15 in J14 then Salary Paid should be Paid by calculating '' total present days + One Leaves Allowed'. But if its below 15 employee should only get total present days NO 'One Leaves Allowed' G14

    3. And if in cell I14 the value is 2 not less then 2 it should deduct 2 days wages/Salary.. Which is now working but not working 'One Leaves Allowed' G14. Its not adding +1 in J14 (total Earned days).

    looking forward for a solution on this. Thank you alot.

    Best,
    Last edited by pipsmultan; 08-13-2019 at 09:06 PM.

  4. #4
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,314

    Re: Stuck in the Subtraction formula :(

    It works "fine" for me, based on my understanding of your intention. You never explained what G14="Yes" should do.

    Please provide an example (values in columns E:I) where my formula provides the wrong answer, and, of course, tell us what the answer should be.

    My interpretation: If G14="Yes", then 1 day of leave is "free"; that is, it does not reduce "present". So if "leave" is 1 and G14="Yes", it is the same as if "leave" is 0. Likewise, if "leave" is 2 and G14="Yes", it is the same as if "leave" is 1. Etc. On the other hand, if G14<>"Yes", the full value of "leave" reduces "present".

    I had thought of another interpretation, but that is not reflected in your original formula at all, to wit: if G14="Yes", "present" is reduced by at most 1 "leave". So if "leave" is 2 or more and G14="Yes", it is the same as if "leave" is 1. Frankly, I cannot imagine offering such a perk.

  5. #5
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    285

    Re: Stuck in the Subtraction formula :(

    Hi Joeu,

    Thank you for kind reply.
    I have edit the P#3 and give a short summary of my problem.

    Best,

  6. #6
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,314

    Re: Stuck in the Subtraction formula :(

    I'm lost. I won't be able to help you further.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    21,558

    Re: Stuck in the Subtraction formula :(

    You have attached an image. That's not very easy to work with. Also... I'm lazy. I have to re-type your information before I can begin to address your problem. That puts me off completely. Not only that, but (depending on their choice of browser) many other members cannot even see the image.

    So. Do yourself a favour and please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough). However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever).

    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.

    Make sure confidential info is removed first!!!!
    Glenn



  8. #8
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    285

    Re: Stuck in the Subtraction formula :(

    Hi Glenn,

    I have attached again SMALL sample Excel workbook as per you guidance. It was attach already before in p#1.
    Please let me know if you still not able to open it.

    Thanks,

    Best,
    Attached Files Attached Files

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    21,558

    Re: Stuck in the Subtraction formula :(

    Apols!! I didn't spot the file on Post 1...

    Try this:

    =IF(E14<15,E14,E14-$F14+IF($G14="Yes",$H14-$I14))

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    21,558

    Re: Stuck in the Subtraction formula :(

    Incidentally, I have just noticed that your thread title is pretty poor. Since we are well into the process of getting you an answer, I shall change it for you. However, as a member with 5 years experience here, you should know that your original thread title would not be acceptable.

  11. #11
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    285

    Re: Stuck in the Subtraction formula :(

    Try this:

    =IF(E14<15,E14,E14-$F14+IF($G14="Yes",$H14-$I14)
    Hi Glenn,
    It doesn't give the desire result in J14
    i.e Earned J14 is 2 because of present E14 is 2, but it doesn't process -F14+G14 (If Yes)+H14-I14

    Thanks for correcting the thread title

    Best,
    Attached Images Attached Images

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    21,558

    Re: Syntax in formula giving incorrect result

    I don't understand you. I see 13 in J4, here.
    Attached Files Attached Files

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    21,558

    Re: Syntax in formula giving incorrect result

    If it's not right, give SEVERAL samples with expected answers (manually calculated) in anther column that is REPRESENTATIVE of all scenarios, not one.

  14. #14
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    285

    Re: Syntax in formula giving incorrect result

    I think i resolved it with below formula:
    Please Login or Register  to view this content.
    but facing one problem in H13. Which is H13 should only be True(or add +1)in J14 if I14 is = 2 (not less then 2 or greater then 2)

    @Glenn please see the SEVERAL samples with expected answers but not calculate manually
    1. Sample image attached
    2. Sample Excel sheet also attached
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by pipsmultan; 08-14-2019 at 05:22 AM.

  15. #15
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    285

    Re: Syntax in formula giving incorrect result

    Quote Originally Posted by Glenn Kennedy View Post
    I don't understand you. I see 13 in J4, here.
    If you put value 14 in E14 then J4 will not calculate/execute your formula

+ Reply to Thread
Page 1 of 2 1 2 LastLast

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