time and value question

Deanius

New Member
Joined
Mar 12, 2017
Messages
14
Hi All, Thanks for any help on this one.

I have a a number of daily logs that show a percentage toner in my cartridge. The percentage falls as i use it over time. I want to be able to see when the cartridge was replaced. But sometimes people can take the cartridge out and shake so the value goes back up to 100% over a few days then goes back to its actual value. EG 15,14,14,13,13,13,12,100,100,100,10,9,9,8,100,100,99,98,97

So i'd like a way of counting if the value is not a shake of the bottle at 12 and only counts one when it is a replacement 8 then 100,100,99,98,97

Thanks hope that makes sense.

Dean
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,738
ask producent of this machine for technical codes and you'll see everything. it doesn't matter people shaking cartidge or not
 

Deanius

New Member
Joined
Mar 12, 2017
Messages
14
ask producent of this machine for technical codes and you'll see everything. it doesn't matter people shaking cartidge or not

Thanks but i'm getting the data from the SNMP and i'd like to look at old data so i need to have a formula to do it now.
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Not sure this is exactly what you want, but :-
Assuming your data string is in "A1" then try this (lightly tested) for results in "A2".

Code:
[COLOR="Navy"]Sub[/COLOR] MG17Feb40
[COLOR="Navy"]Dim[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Sp = Split([a1], ",")
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp)
   [COLOR="Navy"]If[/COLOR] n < UBound(Sp) [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Sp(n) <> 8 And Sp(n + 1) = 100 And Not Sp(n) = 100 [COLOR="Navy"]Then[/COLOR]
         txt = txt & IIf(txt = "", Sp(n), "," & Sp(n))
        [COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] Sp(n + 1) = 100
            n = n + 1
         [COLOR="Navy"]Loop[/COLOR]
     [COLOR="Navy"]Else[/COLOR]
        txt = txt & IIf(txt = "", Sp(n), "," & Sp(n))
    [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]If[/COLOR] Not Sp(UBound(Sp)) = 100 And Sp(UBound(Sp) - 1) <> 100 [COLOR="Navy"]Then[/COLOR]
    txt = txt & "," & Sp(UBound(Sp))
[COLOR="Navy"]End[/COLOR] If
[a2] = txt
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,656
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top