# Syntax in formula giving incorrect result

1. ## 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,  Register To Reply

2. ## 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.  Register To Reply

3. ## 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,  Register To Reply

4. ## 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.  Register To Reply

5. ## 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,  Register To Reply

6. ## Re: Stuck in the Subtraction formula :(

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

7. ## 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!!!!  Register To Reply

8. ## 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,  Register To Reply

9. ## 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))  Register To Reply

10. ## 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.  Register To Reply

11. ## 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,  Register To Reply

12. ## Re: Syntax in formula giving incorrect result

I don't understand you. I see 13 in J4, here.  Register To Reply

13. ## 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.  Register To Reply

14. ## 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  Register To Reply

15. ## Re: Syntax in formula giving incorrect result Originally Posted by Glenn Kennedy 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  Register To Reply

+ Reply to Thread

#### Thread Information

##### Users Browsing this Thread

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

#### 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