Posts by daddylonglegs

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Re: SUMIFS and WEEKNUM


    Quote from NBVC;670204

    =SUMPRODUCT(--(WEEKNUM(Sheet1!$E$2:$E$100)=A8),Sheet1!$H$2:$H$100)


    I don't believe that formula will work (certainly not in Excel 2007) because WEEKNUM function can't handle ranges - in Excel 2007 and later, though, it can handle arrays so you can use +0 to convert like this:


    =SUMPRODUCT(--(WEEKNUM(Sheet1!$E$2:$E$100+0)=A8),Sheet1!$H$2:$H$100)

    Re: How Reduce Formula


    I assume this is a Brazilian version of Excel, or Portuguese so SE = IF and PROC = LOOKUP


    That doesn't really explain the formula, though, because it's a series of IF functions like


    =IF($A20=TOROS!$A$177;LOOKUP($A20;TOROS!$A$177;TOROS!A$232)


    That uses LOOKUP with a lookup range of a single cell (!) so can be reduced to this


    =SE($A20=TOROS!$A$177;TOROS!A$232)


    and you can do the same throughout the formula.


    You could reduce considerably if there was some pattern to the arrangements....but there appear to be slight variations, e.g. the above tests A20 against A177 and if equal returns the value from 55 cells further down the column......but later in the formula this changes to 56...and the cells being tested are also variable distances apart. If you make the pattern consistent then you could use a simple one line formula........

    Re: countif criteria issue


    Don't know about the macro part but in COUNTIF you could simply add those two values as the criteria, e.g. if your data range is column A - your date (for the criteria) is in B2 and time in C2 you can use


    =COUNTIF(A:A,"<"&B2+C2)

    Re: Last Working Day of Month


    Quote from dtully;589640

    =WORKDAY(EOMONTH($D$1,0),IF(WEEKDAY(EOMONTH($D$1,0),2)>5,1,0))


    Is that supposed to give last workday in the month? Surely it doesn't, what if D1 is today?


    This thread is more than 6 years old so I'm not sure whether Cameron is still interested but this is simplest if you use WORKDAY to go back 1 working day from the 1st of the next month, i.e.


    =WORKDAY(EOMONTH($D$1,0)+1,-1)

    Re: Vlookup / networkdays required


    I don't get #N/A when I use Kris' formula on your sample...but there are some strange results because you have rows where the date column is blank - what should happen then - what result do you expect. Also what about the situations when there are 2 or more matches, which date would you want. Is it the latest date?

    Re: VLookup Is Not Corresponding To Table


    mmmm.....


    I don't think sorting is the answer. One problem with that approach is that you can get "false matches" for codes that don't exist, e.g. what result do you get if you put "ZZ" in B20?


    For this sort of situation I assume that only an exact match makes sense.......so the 4th argument of VLOOKUP needs to be included, i.e.


    =VLOOKUP($B20,Rates,2,FALSE)


    With this version the table order is unimportant, it can be unsorted. Now if B20 is "ZZ" the formula returns #N/A

    Re: Future date by adding number of days including all days except the days I specify


    From your profile I see you have excel 2007. For that version you could try this array formula in C1


    =A1+SMALL(IF(COUNTIF(Sheet2!A$1:A$100,A1+ROW(INDIRECT("1:"&B1*10))),"",ROW(INDIRECT("1:"&B1*10))),B1)


    confirmed with CTRL+SHIFT+ENTER


    In Excel 2010 there is an easier formula solution available by using the new WORKDAY.INTL function, i.e.


    =WORKDAY.INTL(A1,B1,"0000000",Sheet2!A$1:A$100)

    Re: SMALL FUNCTION excluding blank cells


    Try this "array formula" in AL92


    =SMALL(IF(TEXT(Table1[Date],"dddd")=Z92,IF(Table1[Date]<>"",IF(Table1[[Early AM]:[Bed Time]]>0,Table1[[Early AM]:[Bed Time]]))),1)


    confirm with CTRL+SHIFT+ENTER and copy down to AL98 to get the minimum non-zero (and non-blank) value for each day. Note you need to correct the spelling of "WEDNESDAY" in Z95.........

    Re: Sumproduct severely slowing down spreadsheet


    How many rows are you referencing with the SUMPRODUCT formulas in your real data?


    It may be that OFFSET is contributing to the inefficiencies - OFFSET is a "volatile" function which means that it recalculates every time something is changed even if data directly affecting that formula doesn't change - INDEX is better, e.g. in C19 copied down


    =SUMPRODUCT($M$5:$M$13,INDEX($G$5:$K$13,0,MATCH($A19,$G$4:$K$4,0)))


    and similar for your other formulas

    Re: IF function returning 0(blank) instead of TRUE.


    Sounds like you are misunderstanding how VLOOKUP works - if you use a formula like this


    =VLOOKUP("x",A2:B10,2,0)


    Then if "x" is found in A2 the formula returns the value of B2 - if B2 is a blank you don't get #N/A, that only occurs if "x" isn't found anywhere in the lookup range (A2:A10). If you want to return some message if B2 is blank then you can do that like this


    =IF(Vlookup_formula="","blank",Vlookup_formula)


    You can replace "blank" with any text you want, even "" (a blank)


    If you also want "not found" then you could add an IFERROR function to that, i.e.


    =IFERROR(IF(Vlookup_formula="","blank",Vlookup_formula),"not found")

    Re: Formula to count unique with 3 criteria.


    Just a small suggestion.....


    rather than "hardcoding" the character length [10] in this part


    LEFT(Workorders!$K$2:$K$25000,10)=Individuals!O$27


    use LEN function, i.e.


    LEFT(Workorders!$K$2:$K$25000,LEN(Individuals!O$27))=Individuals!O$27


    Now you can change cell O27 to anything you want without having to change the formula........

    Re: Calculate Average of First Lowest Scores over a Timespan


    The answer here depends on your setup. Are the scores in a row or a column, are the first scores at the top of the bottom (or left or right). If the player isn't there what goes in the cell for that week, is it left blank or filled with zero, text or something else?


    Assuming you have scores in B2:K2 with week 1 score in B2 and blanks where the player isn't there try this


    =SUM(SMALL(B2:INDEX(B2:K2,SMALL(IF(B2:K2<>"",COLUMN(B2:K2)-COLUMN(B2)+1),4)),{1,2,3}))/3


    That's an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER


    You'll get an error if there are less than 4 numbers - what should happen then, e.g. if there are only 2 or 3 scores?

    Re: Search for value and return text in column header


    Assuming your table is in A1:D5 then with a specific zip code in F2 try this "array formula" to get the relevant header


    =INDEX(A1:D1,MIN(IF(A2:D5=F2,COLUMN(A2:D5)-COLUMN(A2)+1)))


    Formula needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar

    Re: SUMIFS formula w/ variable Sum_Range


    You can simply match B15 against the headers in E3 and F3 (on Data sheet). No need for any other table listing the ranges. Try this formula


    =SUMIFS(INDEX(Data!$G:$H,0,MATCH($B$15,Data!$E$3:$F$3,0)),Data!$A:$A,"P2",Data!$D:$D,$A18)


    It's usually preferable to avoid volatile functions like INDIRECT where possible.......


    regards, daddylonglegs