Formula for finding value differences within a range Giving wrong answers

Before I look at this, would you care to comment on your previous thread (no response from you) and ALSO explain HOW this differs from your previous request (duplicate threads are not permitted).

Thanks, Please explain what you WANT the formula to do. It is hard to follow a non-working formula!! Also, your sample is too big. Manually checking >1000 rows of data is impossible. Upload a sample with 10-20 rows with expected answers. See yellow banner (top) about sample sheets.

See Post 4.

Can you explain what the formula is MEANT to do?

Hi Sir Glenn Kennedy,

Please let me Explain

I have values in column G from cell G5 onward. In cell H5, I have value to compare with values of column G.

• Expected Results in Column I

In cell I5
I want that value of H5 should be subtracted from G6 value i.e. =G6-H5. And if the difference is < 4 And >-4 (4 comes from cell value of G1), Then it should not write this difference value in cell I5. It should move to check next cell difference i.e. =G7-H5. Again if the value lies in this range < 4 And >-4 then it should not write this value in cell I5 and move on to the next difference of =G8-H5. And when the difference comes ≥4 and ≤-4, then it should write this current difference value in cell I5.

In cell I6
I want that value of H6 should be subtracted from G7 value i.e. =G7-H6. And if the difference is < 4 And >-4, Then it should not write this difference value in cell I6. It should move to check next cell difference i.e. =G8-H6. Again if the value lies in this range < 4 And >-4 then it should not write this value in cell I6 and move on to the next difference of =G9-H6. And when the difference comes ≥4 and ≤-4, then it should write this current difference value in cell I6.
And same for onward cells in column I.


(Value of 4 comes from cell G1. And value -4 is just additive inverse of 4 in G1)

Then one of the member (protonLeah) at this excel forum gave me this formula

Formula:

Please Login or Register  to view this content.

Then i asked you about modification that this formula should take range from cells. Now when i insert more values in column G then answers are not as i wanted. Please check where the mistake is.

Put about 6 rows of data in there. Add the expected results, for all 6 calculated manually. I think I follow, but don't want to spend more time doing the wrong thing. Does the existing formula (proton leah) DEFINITELY give the correct result in ALL circumstances??

Yes Sir,

Formula by protonLeah gives exact results that i wanted in all circumstances.

But after modification it is making wrong answers. Please see attached file in post#9 for more clarification

And do not PM members (including Moderators) for help with Excel questions.
Administrative Note:

You PMed me last night 10:31 PM CDST.

Unfortunately, it has come to our attention you have violated Rule #8 of our Forum RULES:

Don't private message, visitor message or email Excel (or Access, Word, etc.) questions to moderators or other members.

All questions and answers will benefit other posters like yourself when discussed in public threads. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.

Breaking this rule is considered harassment by most of our contributors and thus cannot be tolerated. Repeat offense could lead to a permanent ban, so do take this caution to heart.

Post your question in a public thread and our many contributors will come to your assistance, especially if the title is accurate (see Rule #1) and you include a sample desensitized workbook that makes it easy for others to try and help.