+ Reply to Thread
Results 1 to 40 of 40

I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

  1. #1
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Hi, all,

    I have a litle project where I'm trying to explore and categorize patterns in my set of time data according to certain criteria defined by me. This relates to this thread I already made in this forum. Initially, I asked for help counting certain conditions, but I'd also be interested in creating a label for each type of day based on which condition is met.

    Having thought more on it, I'm wondering if perhaps the answer to my thread above is best achieved through what I'm doing here anyway and simply counting the # of patterns.

    I think it's hard for me to describe here, but I hope the Excel sheet is somewhat self-explanatory.

    Basically, I've given a label to each condition/pattern I've created. I've just used AA, AB, AC, etc. Then, I've started a what-if formula where the column pattern returns AA, AB, AC, etc. if those condtions are met.

    For example, with pattern A, I'm requesting that B5, Order = LH; C5, LOD <= 10:00 (10/24); HOD <= 10:00 (10/24).

    By now, I have 3 out of 44 patterns and it looks like this (sorry if the syntax seems weird, but it's Excel in Norwegian. It's correct in the Excel sheet though):

    =HVIS.SETT(OG([@Order]="LH";[@LOD]<=10/24;[@HOD]<=10/24);"A";OG([@Order]="LH";[@LOD]<=10/24;[@HOD]>10/24;[@HOD]<=11/24);"AA";OG([@Order]="LH";[@LOD]<=10/24;[@HOD]>11/24;[@HOD]<=12/24);"AB")

    Which means it will get a helluva lot longer, pardon my french.

    I don't mind spending a day doing this if it works, but I'd love to hear some experienced Excel users comment on this and whether there's a smarter way to accomplish this or even some other software, although I'd love to stay with Excel.

    Thanks a lot in advance for any pointers.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    In the sheet, the formula begins with _xlfn.IFS, which is an incomplete translation of HVIS.SETT. What's it meant to be??
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    this is IFS
    i have 2016, and it does not know this function
    for TS
    if you do the following:
    now
    13:00-14:00
    should be
    13:00
    14:00
    the rest will be easy.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    I get it now. If you can rearrange your coding into a coherent dataset, then a straightforward formula will do it. I didn't rearrange all your data, just in case it's not possible to do so in your real sheet; but you can see how it works.

    =IFERROR(INDEX($L$6:$L$17,MATCH(1,INDEX(($G$6:$G$17=[@Order])*($H$6:$H$17<[@LOD])*($I$6:$I$17>[@LOD])*($J$6:$J$17<[@HOD])*($K$6:$K$17>[@HOD]),),0)),"")

    also... see the green shaded cells. Sometimes 12:00 to 13:00 is included... sometimes it isn't. Was that intentional??
    Attached Files Attached Files

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Thanks Tim... I'm still in the dark ages, with excel 2013.... No IFS for me.

  6. #6
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Hi all,

    Thanks for the quick comments. I'm using Microsoft Excel 2016 and I've uploaded numerous sheets on this forum and the formulas seem to have translated well earlier? I think the "Hvis.Sett" translate to the function IFS. The "OG" translates to the fuction "AND" I think.

    Also, I wrote 10:00 as 10/24 which should equal the numerical value, I think? I saw that in the other thread where I got help and figured it was just as easy to write out.

    Pattern A requires Order = LH and LOD in the range 09:30-10:00 with the second letter deciding if the HOD is in interval 09:30-10:00, 10:00-11:00, etc.

    Pattern B requires Order = LH and LOD in the range 10:00-11:00 with the second letter deciding if the HOD is in interval 09:30-10:00, 10:00-11:00, etc.

    Etc.

    Below the black line I have the same thing in reverse with Order = HL and now HOD 09:30-10:00 with LOD at certain other intervals. Etc.

  7. #7
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    =INDEX(l!$E$5:$M$180,LOOKUP([@LOD],l!$C$5:$C$16/([@Order]=l!$B$5:$B$16),l!$A$5:$A$16),LOOKUP([@HOD],l!$E$3:$K$3,l!$E$1:$K$1))
    Attached Files Attached Files
    Last edited by tim201110; 01-14-2018 at 03:21 AM.

  8. #8
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Quote Originally Posted by Glenn Kennedy View Post
    I get it now. If you can rearrange your coding into a coherent dataset, then a straightforward formula will do it. I didn't rearrange all your data, just in case it's not possible to do so in your real sheet; but you can see how it works.

    =IFERROR(INDEX($L$6:$L$17,MATCH(1,INDEX(($G$6:$G$17=[@Order])*($H$6:$H$17<[@LOD])*($I$6:$I$17>[@LOD])*($J$6:$J$17<[@HOD])*($K$6:$K$17>[@HOD]),),0)),"")

    also... see the green shaded cells. Sometimes 12:00 to 13:00 is included... sometimes it isn't. Was that intentional??
    Hi, Glenn,

    Thanks a lot! Fantastic.

    No, it wasn't intentional at all. Simply an error of mine this morning being tired and all. Well spotted.

    As for what you did, I have to say that your formula is well over my head, although it does seem like a very clever solution. I'm not familiar with those formulas you've used at all. Would it be possible for you to explain it a little better or point me towards where I could read a little more on it?

    Thanks again.

    Elijah

    PS: I'm also considering implementing a further condition which is if the Close (column A) is positive or negative. So, if positive, I might say it's an A1. If it's negative, I might call it A0.

    AA1, AA0, AB1, AB0, etc.

    Would adding such a condition be possible without too much trouble also? I can figure out what you started first without this additional condition, but it would be nice to explore later for sure.
    Last edited by Elijah; 01-13-2018 at 09:29 AM.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Im at the cinema. Back later...

  10. #10
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    I'm still in the dark ages
    Glenn, i could give a link
    an edition for MS' friends only
    of cause, free of charge

  11. #11
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Your HL's did not have any matching times i threw in some times for HL just to make sure this worked.Click the button.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by davesexcel; 01-13-2018 at 09:55 AM.

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    =IFERROR(INDEX($L$6:$L$17,MATCH(1,INDEX(($G$6:$G$17=[@Order])*($H$6:$H$17<[@LOD])*($I$6:$I$17>[@LOD])*($J$6:$J$17<[@HOD])*($K$6:$K$17>[@HOD]),),0)),"")

    Red: Return values from this range
    Gold: where the following conditions are all true:
    Green: G6-G17 equals the value in "order", AND
    Cyan: the LoD timefalls in this range. AND
    Blue: The HoD time falls in this range.

    The bit in grey turns what would have been an array formula into an ordinary one

    and the IFERROR bit returns a blank instead of an error message.

    In the attached sheet, I added on another term: &IF([@Close]<0,0,1)

    which is fairly obvious and does what you asked for in your last comments to me.
    All you need to do is complete the table and adjust the ranges in the formula to suit.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Quote Originally Posted by tim201110 View Post
    =INDEX(l!$E$5:$M$180,LOOKUP(2,1/([@Order]=l!$B$5:$B$16)/([@LOD]>=l!$C$5:$C$16)/([@LOD]<=l!$D$5:$D$16),l!$A$5:$A$16),LOOKUP(2,1/([@HOD]>=l!$E$3:$K$3)/([@HOD]<=l!$E$4:$K$4),l!$E$1:$K$1))
    Thanks a lot, Tim!

    There does seem to be a few errors on my sheet? But I love how you were able to make a formula without any additional tables (not a major problem, but if I were to integrate this with some other tables, it would be an advantage to have just one formula to relate to).

  14. #14
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Quote Originally Posted by davesexcel View Post
    Your HL's did not have any matching times i threw in some times for HL just to make sure this worked.Click the button.
    Thanks a lot, man. This is a very elegant solution indeed. A disadvantage for me is that I don't know VBA, so I won't probably be able to do any editing on my own if I am to update it. But, I suppose I could learn it.

    Curious what you mean with HL not having any matching times...?

    Did you also reverse the C and D column for HL?

    For instance, pattern A- happens when the HOD (column D) is at 09:30-10:00 and the LOD is at 09:30-100.

    Anyway, thanks a lot. I feel I'm being very spoiled here.

  15. #15
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Quote Originally Posted by Glenn Kennedy View Post
    In the attached sheet, I added on another term: &IF([@Close]<0,0,1)

    which is fairly obvious and does what you asked for in your last comments to me.

    All you need to do is complete the table and adjust the ranges in the formula to suit.
    Hi, Glenn,

    Thanks a lot. I did try to update the sheet, but I get a lot of blank cells, so I'm probably messing up somewhere. Care you help me out with the rest? I'm attaching the sheet where I updated all the values correctly, I think.

    PS: If I should decide I don't want the additional Close +/- condition, is it as simple as removing this one at the end...?

    Best regards.
    Attached Files Attached Files

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    LoD 09:30-10:00 and then 10:00-11:00. At 10:00, which time group should it fit into?

    I guess I'm asking... do you REALLY want

    09:30-10:00 and 10:01-11:00 or what??

  17. #17
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Quote Originally Posted by Glenn Kennedy View Post
    LoD 09:30-10:00 and then 10:00-11:00. At 10:00, which time group should it fit into?

    I guess I'm asking... do you REALLY want

    09:30-10:00 and 10:01-11:00 or what??
    Hi, Glenn,

    I guess the important thing is to avoid a double count moving from one interval to the other. What I had in mind was this:

    <= 10:00 (09:30-10:00)

    > 10:00 & <= 11:00 (10-11:00) which would be 10:01 to 11:00

    > 11:00 & <= 12:00 (11-12:00)

    > 12:00 & <= 13:00 (12-13:00)

    > 13:00 & <= 14:00 (13-14:00)

    > 14:00 & <= 15:00 (14-15:00)

    > 15:00 & <= 16:00 (15-16:00)

    Thanks again.

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Try this. Any/all gaps are for times when Lod is AFTER HoD
    Attached Files Attached Files

  19. #19
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Excellent, Glenn. Many thanks!

    That seemed to sort it all out for the LH patterns with LOD preceding HOD.

    However, a few of the "-" patterns with the HOD preceding the LOD were also included. Seems like it were those were the HOD/LOD happened in the same interval?

    1. Would it be possible to not include the "-" patterns for the formula you included?

    2. I'm interested in making the same calculation for the HL patterns also. I created a new column where I reversed HOD/LOD in your formula and it seemed to work. : )

    I think the best however would be if I could combine it all in one formula. If not, it would be nice to at least isolate them (HL/LH) in one column each.

    Any pointers?

    I feel like I'm getting too much help now, but it would be great to have this work 100% being this close.
    Attached Files Attached Files

  20. #20
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Back in a couple of hours....

  21. #21
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    OK. Try this out:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  22. #22
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Hi, Glenn,

    Once again thank you so much.

    I did a quick check and it seems like all the codes are indeed correct. The only problem is that with the rows/days with HL, there are no "-" behind to differentiate it from the LH days.

    If that's not so easy to accomplish, no worries. I can sort the LH/HL separately and export to separate sheets. So, either way, I'm very happy with the result and all help.

    Best regards and thanks.

    Elijah

  23. #23
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Im out for the night. A quick look: preceed 3rd Index with "-"& should do it. If not, I'll check in am.

  24. #24
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Hi, Glenn,

    I tried that, but the only thing that happened is that the HL days pattern disappeared.

    Anyway, no worries.

    Enjoy your night out.

  25. #25
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Here you go...
    Attached Files Attached Files

  26. #26
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Hi, Glenn,

    Thanks again and I really hate to bother you more at this point, but it does seem like there's still some conflict between the LH/HL values and the patterns get mixed up.

    If you sort out LH days, you'll see that there are many days with minus-es. Also, if choosing for example -AD, you'll see that there's a mix between LH/HL.

    The time slots seem to be correct; it's just that the minus ain't consistent.

    If there isn't a fix for this, I can simply create two columns and change up the formula: 1 for LH pattern and one for HL pattern.



    Best regards,

    Elijah
    Attached Images Attached Images

  27. #27
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    @Elijah

    It's somewhat annoying to find this thread which seems exactly the same subject as in your other thread that you started on 12 January.

    We have a forum rule about duplicating posts so that we can avoiding people wasting their time. With 105 points you really should be aware of this and indeed all our rules.

    You should not be raising essentially the same question in another thread just because you don't want to use a suggestion you have been given.

    Please acknowledge youj have understood this.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  28. #28
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Hi, Richard,

    My apologies if I have violated any rules, but I wouldn't say that it's exactly the same subject although it's related. Initially, I wanted to simply count, but for sorting purposes, it would be nice to label each condition which was the purpose here in this thread.

    That's why I wrote thank you to you in that other thead and also said that I realized that I would be able to accomplish the count with my second spreadsheet instead and that you shouldn't bother with that one.

    Feel free to delete the other thead if that helps. And again: my apologies for this.

  29. #29
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    You hadn't made it clear (at least to me...) that it was - and HL. I just made all the -ves return a minus... Try it now.
    Attached Files Attached Files

  30. #30
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Hi again, Glenn,

    And thanks for your continued effort and patience.

    I completely understand if you're fed up by now, but I think it's still not quite right. The # of patterns without a minus doesn't equal the number of LH rows, so there might be a double count somewhere?

    Also, if you sort the HL rows only, you'll see that there are several rows without a minus as well. For instance AA and -AA.

    I can summarize the logic as follows:

    1. What we have here is time data where I try to categorize the rows based on certain criteria.

    I made my own preliminary filter LH/HL which basically says that if LOD < HOD = LH, if not: HL. Basically, it tells if the LOD happened before the HOD for that day/row of data.

    2. Next, I decided to create patterns based on in which interval the LOD/HOD is found. I decided to use first 30 minutes and then hourly intervals for the rest of the day.

    3. The LH/HL patterns are simply inverse of each other.

    Pattern A = Both LOD/HOD <= 10:00

    Pattern A- = Both LOD/HOD <= 10:00

    Pattern AA = LOD <=10:00 & 10:00 < HOD <= 11:00

    Pattern AA- HOD <=10:00 & 10:00 < LOD <= 11:00

    ***

    If the minus is a problem, I can just as well find a different name/code for the patterns in column L. You'll see that I simply used the same abbreviation for the HL patterns with a "-" at the end since they're inverse of each other.

    Alternative, I could for instance label them LH_AA, LH,_AB, HL_AA, HL_AB, etc.

    But, maybe the best solution is to simply create to separate columns for HL/LH patterns and switch the HOD/LOD in the formula.

    Best regards,

    Elijah
    Attached Files Attached Files

  31. #31
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Hi. We'll get there. At least the numbers add up now. try this.
    Attached Files Attached Files

  32. #32
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Hi, Glenn,

    Many, many thanks! I really appreciate this. I tried giving you reputation again, but wasn't allowed (yet).

    It does indeed seem to work now.

    I have not had time to check it out in depth, but I did some checking and could not find any errors. I only wish I knew exactly what you've done such that I can reproduce similar sheets, but I'll see if I can get time to study it later. Looking at the formula, I seem to be able to follow some of it.

    Thanks again.

    Best regards.

  33. #33
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Hi again, Glenn and others,

    I did some more work on this and ended up with this (attached).

    My current sample is 1503 days. It would be interesting to for instance look at the last 100 days instead.

    One solution would simply be to make a copy of this sheet and paste the last 100 days.

    But would it be possible to have a dynamic lookback period on this formula?

    In this thread I learned how to do that and have set up all my other sheets this way with a reference cell where I can choose lookback period.

    No reason to spend any time on this, but if it's possible without too much trouble, why not.
    Attached Files Attached Files

  34. #34
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Do you mean to adjust the formula in column M to look back 100 days.... or do you mean something else?

  35. #35
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Hi, Glenn,

    Yes, that would pretty much be it.

    For some reason, I figured you'd have to change the formula in column E, but you're right that it's the count in column M that matters.

    I do suppose I could set it up using the very same trick I learned in that thread I linked to, so I don't think you need to worry about this.
    Last edited by Elijah; 01-23-2018 at 01:52 PM.

  36. #36
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    I'd just adjust column M, myself.... Shout if you run into the sand...

  37. #37
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Your kindness is appreciated, Glenn, but I do think I managed to pull it off.

  38. #38
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Woo Hooo!!

  39. #39
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Hi all,

    Instead of making a new thread, I'm posing a similar question in this thread.

    Basically, I have a large set of data composed of separate days (rows) and multiple columns (metrics/variables). Filtering/sorting this data can be time consuming and complex , so I got the idea that I could summarize several conditions in one variable/metric.

    Thanks to the great help from GlennKennedy, I was able to create a 'pattern' metric based on three variables. This has made it so much easier to sort through my data and I can even quickly visualize that day based on what that metric alone tells me.

    To summarize.

    I have variable 1 = LH, variable 2 (time data) = 10:15 and variable 3 (time data) = 15:55. Now, I chose to summarize this into one variable/metric which simply reads: "LH_10-16:00"

    Obviously much faster to filter and also faster for my brain to process.

    I'm now interested in possibly taking this a step further and including even more variables...

    So, my question is basically if this is possible using the same type of formula? Or are there limits to the amount of variables one may include?

    Thanks in advance for all help!
    Last edited by Elijah; 05-29-2018 at 04:01 PM.

  40. #40
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,421

    Re: I'm writing out a very long What-If Formula in Excel - Possible? What are the limits?

    Elijah - on this occasion, it would be better to start a new thread. By all means post a link back to this thread if you think it relevant.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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. Writing long array formula in VBA?
    By maym in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-28-2017, 02:44 AM
  2. [SOLVED] writing a long number
    By Mukapa in forum Excel General
    Replies: 4
    Last Post: 04-29-2016, 08:05 AM
  3. [SOLVED] Is there a shorter, more elegant way of writing this? [long function]
    By johanna0507 in forum Excel General
    Replies: 2
    Last Post: 11-01-2013, 11:07 AM
  4. [SOLVED] What is easier? writing many sub routines or just one long one.
    By damayn83 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-18-2013, 05:49 PM
  5. Excel 2007 formula argument limits
    By Carmen.Hayter in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-04-2012, 01:23 PM
  6. [SOLVED] Syntax for Writing Long VBA Formula on Multiple Lines
    By nwd9s in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-13-2011, 06:38 AM
  7. excel formula with 3 or more upper and lower limits
    By roraniel in forum Excel General
    Replies: 4
    Last Post: 01-03-2009, 02:47 PM

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