Finding the last increase/decrease of an series of data (row)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a row of data:
1 5 2 6 8 9 0 0 0 0 0 0 0 0 X (example .. 31 of them, one for each day)

every day, one value is entered. for today the value is "9". I need a
formula that will automaticly calculate the % increse or decrese of the last
added value (8-9 in %). The header of these colums can be DATES and we might
use TODAY to locate and calculate the increse/decrese... or maybe we can read
all of the values into an array... i do not know... i need help please.

X can be the output of the last increse or decrese

i am not flewent with vba so be detailed in your guidence please.

Justin
 
Sub Increase()
Dim iLast As Long
On Error Resume Next
iLast = Application.Match(0, Range("A2:AF2"), 0)
On Error GoTo 0
If iLast = 0 Then iLast = 32
MsgBox Format((Cells(2, iLast - 1).Value - Cells(2, iLast - 2).Value) /
_
Cells(2, iLast - 1).Value, "0%")
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Bob,

Thanks, but I need some more guidence here.

I need it to place this increse (in %) in a cell... perhaps in "AG".

When I use the code as you have given it, it does not like the mgs box...

justin
 
NG wrap-around problem

Sub Increase()
Dim iLast As Long
On Error Resume Next
iLast = Application.Match(0, Range("A2:AF2"), 0)
On Error GoTo 0
If iLast = 0 Then iLast = 32
Range("AG2").Value = Format((Cells(2, iLast - 1).Value - _
Cells(2, iLast -
2).Value) / _
Cells(2, iLast -
1).Value, "0%")
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Bob,

I tried it again... but it still fails. I used it like this:

Sub Increase()
Dim iLast As Long
On Error Resume Next
iLast = Application.Match(0, Range("A1:AE1"), 0)
On Error GoTo 0
If iLast = 0 Then iLast = 32

Range("AG1").Value = Format((Cells(2, iLast - 1).Value - _ Cells(2, iLast -
2).Value) / _ Cells(2, iLast - 1).Value, "0%")
End Sub
 
The wrap-around issue is that the newsgroups break lines at a certain point
and write the rest to a new-line. The problem is that I don't see it when I
post, only after it is posted, and it happened again.

Try this version

Sub Increase()
Dim iLast As Long
On Error Resume Next
iLast = Application.Match(0, Range("A2:AF2"), 0)
On Error GoTo 0
If iLast = 0 Then iLast = 32
Range("AG2").Value = Format((Cells(2, iLast - 1).Value - _
Cells(2, iLast - 2).Value) / _
Cells(2, iLast - 1).Value, "0%")
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Sorry, adjusted to your range

Sub Increase()
Dim iLast As Long
On Error Resume Next
iLast = Application.Match(0, Range("A1:AE1"), 0)
On Error GoTo 0
If iLast = 0 Then iLast = 32
Range("AG2").Value = Format((Cells(2, iLast - 1).Value - _
Cells(2, iLast - 2).Value) / _
Cells(2, iLast - 1).Value, "0%")
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Bob,

I thought and looked at your code again... please note that there will be
days when the value for the day IS ZERO.

it could be: 5 3 0 4 5 0 3 0 5 000000000000....

every colum COULD have the header of the DATE if we could use TODAY to look
up the correct colum... just a thought.... OR we can make sure the days that
have not been entered yet for the month is BLANK!

justin
 
If there are embedded zeroes should we step over them? And if so and the
last two numbers are 0 and 9, should we use the last two non-zero numbers?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
we always want to measure increase or decrese since yesterday to today. so if
yesterday was 0 and today is 1, the indrease is 1... if yesterday was 1 and
today is 0, then the increase/decrease is -1... if today is 0 and yesterday
was 0... we have a 0 increase/decrese... is today is 1 and yesterday was 1
the increase is 0

makes sense?

thanks for all this help bob...
justin
 
It does, but what is a percentage of 1/0 or 0/1?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
1 to 0 will be -1 ... and 0 to 1 will be 1



Bob Phillips said:
It does, but what is a percentage of 1/0 or 0/1?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
Click to expand...
 
Sub Increase()
Dim iLast As Long
Dim nLast As Long
Dim nPrev As Long
Dim nAmount As Double
Const Test_Row As Long = 1 '<==== change to suit

On Error Resume Next
iLast = Cells(Test_Row, 32).End(xlToLeft).Column
On Error GoTo 0
If iLast = 0 Then iLast = 31
nLast = Cells(Test_Row, iLast).Value
nPrev = Cells(Test_Row, iLast - 1).Value
If nLast = 0 Or nPrev = 0 Then
nAmount = 1
Else
nAmount = (nLast - nPrev) / nLast
End If

Range("AG" & Test_Row).Value = Format(nAmount, "0%")
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Bob,

Thanks... I saved the code and will look at it when I get back from
vacation. I appreciate your help. i will let you know how it worked. I am
back on the 13th

justin
 
Back
Top