Formula with conditons

D

dhampatel

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
 
B

Bearacade

Hey Dham, can you clarify?

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

David Biddulph

dhampatel said:
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.
Click to expand...

Did you mean A1-B1 as you said, or did you intend to say B1-A1? In that
case your B1-A1 would return -294, and if you use
=MOD(B1-A1,300)
this would return 6.
 
S

Spreadsheet

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.
 
D

dhampatel

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.

Thanks in Advance

Dham
 
Top