+ Reply to Thread
Results 1 to 16 of 16

Switching logic

  1. #1
    Forum Contributor
    Join Date
    01-07-2006
    Location
    San Jose, CA
    MS-Off Ver
    2007, 2016
    Posts
    115

    Switching logic

    Hi,

    In the attached workbook, I created some simple logic to switch between two states based on the data values in columns B and C. In column D, the logic requires that the condition be true for one data point for the switch to occur. In column E, the formula requires two consecutive samples be true for the switch.

    What I really need are two things:

    1) The "On" and "Off" controls have separate, independent logic
    2) The number of consecutive cells which must true for the "On" and "Off" controls can be changed via a cell value; also independent.

    Could anyone in the Forum help with a cell formula that could do this?

    Any help would be greatly appreciated.

    Thanks,

    -Art
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Switching logic

    You can use cell G1 to record how many consecutive cells to group, then use this formula in E2:

    =IF(COUNTIF(D2:INDEX(D:D,ROW(D1)+$G$1),1.5)=$G$1,1.5,2.5)

    Copy down as required.

    So, if you put 3 in G1 the formula in D2 will look to see if cells D2 to D4 inclusive all contain the value 1.5, and will return 1.5 to that cell if the condition is true, otherwise 2.5. Is that what you want?

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    01-07-2006
    Location
    San Jose, CA
    MS-Off Ver
    2007, 2016
    Posts
    115

    Re: Switching logic

    Hi Pete,

    Thanks for your response and formula suggestion. Your formula works as written, however it's not quite what I thought I wanted. If I make G1 value of 50, the 1.5 to 2.5 transition transition occurs earlier on the chart. This works great. I realize now that I need to go both directions (before and after) of the crossover point; I tried entering a -50 in G1 hoping that the formula would do that but it does not. I definitely need a +/- control to move the transition above or below the crossing point.

    Also, I need a similar +/- control for the 2.5 to 1.5 transition (negative going transition).

    Is it possible to add these to the cell formula?

    Thanks,

    -Art

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Switching logic

    ArtZ

    Administrative note
    Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.

    The same holds true for location. There are regional differences in Excel. 'Country' is specific enough for privacy issues.
    Thanks
    Dave

  5. #5
    Forum Contributor
    Join Date
    01-07-2006
    Location
    San Jose, CA
    MS-Off Ver
    2007, 2016
    Posts
    115

    Re: Switching logic

    Hi Dave,

    Done.

    -Art

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Switching logic

    Thank you. That helps much.

  7. #7
    Forum Contributor
    Join Date
    01-07-2006
    Location
    San Jose, CA
    MS-Off Ver
    2007, 2016
    Posts
    115

    Re: Switching logic

    Hi Pete,

    Are you still thinking about the comments in my last post?

    Thanks,

    -Art

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Switching logic

    I must admit that I don't really understand the scenario, but if you wanted a +/- facility and you had g1 set to 50, then you would have to start the formula in row 26 and look back to D2 and forward to D51 and see if all those values were equal to 1.5 for 1.5 to be returned instead of 2.5. The values in the cells E2 to E25 would be indeterminate in this case, though you might want to set them to the corresponding value in column D. Here's a formula to do that (entered into E2):

    =IF(ROWS($1:1)*2<$G$1,"",IF(COUNTIF(INDEX(D:D,ROWS($1:2)-INT($G$1/2)+1):INDEX(D:D,ROWS($1:1)+INT($G$1/2)+1),1.5)=$G$1,1.5,2.5))

    When you copy this down it will take a while to calculate, and if you want the first 25 values not to be blank you can change the "" to D2 instead.

    I'm not really sure about the 2.5 to 1.5 transition, as your original formulae only set the 1.5 value if all the values in the range were 1.5, and if not then it returned 2.5, but see if the new formula is more along the lines of what you were expecting.

    Hope this helps.

    Pete

  9. #9
    Forum Contributor
    Join Date
    01-07-2006
    Location
    San Jose, CA
    MS-Off Ver
    2007, 2016
    Posts
    115

    Re: Switching logic

    Hi Pete,

    Thanks again for your response and cell formula. I will try try out this new cell formula tomorrow; I get why you need to start the calculation later in the series or return blanks until the data for a negative number in G1. You can't calculate with data that hasn't happened yet. However, I think that the confusion point is really quite simple. The value in cell G1 dithers the crossover point for the transitions.

    What I need is a G1 and and say an H1 value that lets me set the dither around the transition point depending on which direction the signal is going. Right now, G1 sets this dither point for both negative to positive (1.5 to 2.5) and positive to negative (2.5 to 1.5) transitions. I need these transitions to have separate control. Does this make sense?

    Thanks,

    -Art

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Switching logic

    Quote Originally Posted by ArtZ View Post
    ... The value in cell G1 dithers the crossover point for the transitions ...
    But this sentence means absolutely nothing to me !!

    I'll have a think about it, but at the moment I have:

    IF(something_based_on_G1, 1.5, 2.5)

    If I replace the 2.5 with another IF based around H1, then it would look like this:

    IF(something_based_on_G1, 1.5, IF(something_based_on_H1, 2.5, what?))

    I'm not sure what the what? should be, as it will have failed both tests at that point.

    Pete

  11. #11
    Forum Contributor
    Join Date
    01-07-2006
    Location
    San Jose, CA
    MS-Off Ver
    2007, 2016
    Posts
    115

    Re: Switching logic

    Hi Pete,

    Let's see if I can alleviate the confusion around the logic. So in simplest non-Excel terms it would go like this:

    If (B2>C2 and B3>C3... (and so on depending on number of included data points, i.e., G1), value = 2.5, otherwise do nothing) OR If( B2<C2 and B3<C3... (and so on depending on number of included data points, i.e., H1), value = 1.5, otherwise do nothing)

    This allows the crossing point to be set independently for ↑ transitions and ↓ transitions.

    Please let me know if this is any clearer.

    Thanks,

    -Art

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Switching logic

    OK, here's another go.

    Put this formula in cell F1:

    =COUNT(A:A)

    It will give you a count of the number of records that you have, as it occurred to me that as well as having the indeterminate values at the beginning of the list (i.e. cells on rows 2 to 25 if G1 is set to 50), there will be a similar number of indeterminate cells at the end of the list (i.e. the last 25 records), so this helps to set this up.

    Use G1 and H1 for the number of consecutive cells that you want to consider, and then put this formula in G2:

    =IF(OR(ROWS($1:1)*2<$G$1,ROWS($1:1)+INT($G$1/2)>$F$1),"",IF(COUNTIF(INDEX(D:D,ROWS($1:2)-INT($G$1/2)+1):INDEX(D:D,ROWS($1:1)+INT($G$1/2)+1),1.5)=$G$1,1.5,2.5))

    This is similar to before, apart from the bit in red which is detecting the end of the list. Copy this down to the bottom of your data, then wait a few seconds for it to calculate.

    Then you can put this formula in cell H2:

    =IF(OR(ROWS($1:1)*2<$H$1,ROWS($1:1)+INT($H$1/2)>$F$1),"",IF(COUNTIF(INDEX(D:D,ROWS($1:2)-INT($H$1/2)+1):INDEX(D:D,ROWS($1:1)+INT($H$1/2)+1),2.5)=$H$1,2.5,1.5))

    which is similar, but is now taking account of the number in H1 and it is looking for consecutive values of 2.5, returning 2.5 if they are all the same or 1.5 if any are different. Again, copy this down to the bottom of your list.

    Then you can use this formula in cell E2 (which drives your square wave graph):

    =IF(OR(G2="",H2=""),NA(),IF(AND(G2=1.5, H2=1.5),1.5,2.5))

    and copy this down to the bottom. Notice that the formula will return #N/A for the indeterminate cells, and these will be ignored by your graph.

    Let us see if this is what you are after.

    Pete

  13. #13
    Forum Contributor
    Join Date
    01-07-2006
    Location
    San Jose, CA
    MS-Off Ver
    2007, 2016
    Posts
    115

    Re: Switching logic

    Hi Pete,

    Thanks for taking another stab at the cell formula. I attached the worksheet where I made your changes for your edification; am fairly sure that I've gotten it right. Observations:

    1) G1 and cannot be less than 6 otherwise switching trace does not alternate between ↑ transitions and ↓ transitions. Need to enter down to zero which should plot the transitions at exactly the cross points.
    2) Changing G1 affects crossings for both ↑ transitions and ↓ transitions.
    3) Changing G2 does not seem to have any effect except below 6.
    4) If I enter a negative number into G1 or G2 to move in the other direction from the crossover point, it does show ↑ transitions and ↓ transitions, and G1 or G2 show #VALUE.

    Thanks,

    -Art
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    01-07-2006
    Location
    San Jose, CA
    MS-Off Ver
    2007, 2016
    Posts
    115

    Re: Switching logic

    Oops, 4) should have read: 4) If I enter a negative number into G1 or G2 to move in the other direction from the crossover point, it does not show ↑ transitions and ↓ transitions, and G1 or G2 show #VALUE.

  15. #15
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Switching logic

    You have put the formulae in the right places, but they don't seem to be having any effect on the square wave.

    I don't understand why you might want to put zero or a negative number in G1 (or H1). You originally asked if there was a way of comparing a number of adjacent cells rather than the two that you had in the first file. I suggested using G1 to specify how many adjacent cells and gave you a formula to act upon this. You then asked if this could act on cells either side of the current cell, so I gave you a way of doing that, so that 50 would compare cells from rows 2 to 51 either side of the cell in row 26 (then 3 to 52 on cell 27, and so on). The concept of having a negative or zero range doesn't really tie in with comparing consecutive cells.

    Maybe I've got the wrong end of the stick, but I'm still not sure what you are trying to do. You seem to have a different understanding of what the value in G1 is used for, so maybe you can give some clearer explanation.

    Pete

  16. #16
    Forum Contributor
    Join Date
    01-07-2006
    Location
    San Jose, CA
    MS-Off Ver
    2007, 2016
    Posts
    115

    Re: Switching logic

    Hi Pete,

    The G1 value has an effect but can't be set less than 6 with your current cell formula. Is the midpoint (transition right at crossing?) I didn't realize that. So if I understand your implentation, a G1 value of 25 puts us right at the crossing, correct? I don't see the transition signal changing in time +/- in time around the G1 value Where 0-25 puts the transition signal on one side of the crossing and 25-50 puts the transition signal on the other side of the crossing. (relative to time of course)

    Yes, the G2 value apparently has no effect unless it's less than 6. I just tried negative values to see if that works and obviously your cell formula doesn't support that.

    Again, in simplest terms, I want to have the crossover signal shifted back or forward in time by any number of samples for both the ↑ transitions and ↓ transitions.

    That's all I am trying to do in all this Does that make sense?

    Also, are these calculations better done in VBA?

    Thanks,

    -Art

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Avoid Switching Sheets, Reference Sheet Without Switching Instead.
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-29-2017, 06:59 PM
  2. Chart Switching
    By caffeineaddict in forum Excel General
    Replies: 4
    Last Post: 04-30-2015, 12:31 AM
  3. Considering switching to a Mac
    By GlenSydney in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 05-09-2014, 09:03 AM
  4. Excel 2007 : Switching within a column
    By TRACYK in forum Excel General
    Replies: 4
    Last Post: 02-17-2012, 08:18 PM
  5. Switching the axis
    By omeiron in forum Excel General
    Replies: 1
    Last Post: 12-12-2011, 06:51 AM
  6. Switching Ink On or Off
    By ACM2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-04-2010, 05:16 PM
  7. switching last name/first name
    By charlie11k in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-31-2009, 11:13 AM

Bookmarks

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