Formula with conditons

1. Formula with conditons

Hi I need your help creating a formula the will subtract two colums. For example it will take cell A1 - B1 but here is the twist. The range of the subraction goes from 1-300 thats it. So when it reaches 300 it resets to 0, but how can create a forumal that will do the computation. For example A1 is 295 and B1 is 001 the formula should compute and spit out 6, but how do I create that. I am trying to figure this one out for past few weeks without any luck. Any help would really help me out and save me lot of time from doing manual calcualtions.

Thanks,

Dham

2. Hey Dham, can you clarify?

From your example, 295 and 1 is 296, how did you return 6?

3. Modular

If I understand correctly, Dham wants to solve for x in the equation:

MOD(295+x,300)=1.

In other words, what has to be added to 295 to get 1 if we are resetting to zero when it reaches 300.

MOD(295+6, 300)=1 makes sense for the example given.

This however, is not solvable without more information. If you know that 300 was only reached once before resetting to 0, then the formula would be
C1=B1+300-A1.

4. This is what I am trying to calculate. I have a ticketing program that customer requests automatically goes into. Here is the quarkiness part of the program the ticketing program stops at 999 then resets at 001. So for example day before yesterday the ticket system was at 840 and yesterday's tickets were at 980 and today tickets system is at 100. So what I have is formula that subtracts 980-840=140 tickets for that day [=SUM(b1-a1)]. So what I am trying to do is calculate how many tickets I had for that day. Now here is the problem I can't seem to resolve. is when the system today reached at 100 how do I calculate that? 100-980=??? is not going to work. So I need some kind of IF THEN statements or some other way of calculating that. Currently I do it manually, but need to automate this to save time. Let me know if someone has any idea.

Dham

5. Try

=MOD(B1-A1,1000)

6. ....or, more likely, if 999 goes straight to 1....

=MOD(B1,A1,999)

7. Thank you for your help that worked like a charm.

Dham

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