Search results

Hmm, i get your point from a practical standpoint the method used makes little difference. But since this was the basis of the original question, i was curious to investigate further and thought it might allow for a better understanding of what underpins some of the formulas posted on sites like...

@NARAYANK991, i see how others might have been mislead by what i wrote before. Here's an attempt to give some more details and explain results from your sample data but i fear my explanations are much less enlightening than yours. First, regarding VLOOKUP with Range_Lookup=TRUE, i agree this...

Indeed. A possible alternative to a formula like: =LOOKUP(C1,A1:A4,B1:B4) for finding an approximate match in numeric data that need not be sorted is: =LOOKUP(1,1/FREQUENCY(-C1,-A1:A4),IF({1},B1:B4)) eg with A1:A4={1,5,8,3} B1:B4={1,2,3,4} C1=4 the first formula returns 1 and the second 4.

Hi Somendra - good info on lookup values to use to match the last entry. You highlight that data should be in ascending order with an inexact match, as it says in MS help, but then also raise one (perhaps the only?) case where you can use unsorted data, as Colin describes in the third example...

Thanks for posting this question and the insightful explanations given in the responses, i had never given this much thought before. Here is another simple test which indicates the binary search method is used by lookup functions (when the match_type is not exact): =MATCH(1,{0;1;0;1;0;1;0})...

Option 1 implicitly refers to the ActiveSheet and so will return an error if the workbook containing the code is not active when the code is run. Option 2 doesn't depend on which sheet is active. To get the same results you can make one of the following changes: - In Option 1 insert...

Narayank is correct that the clearest way to do this would be to list all dates on a separate sheet with holidays and hours worked next to each one and sum these up. But if you want to do this using a single formula here is one possibility (not too thoroughly tested)... Suppose you want to find...

=NETWORKDAYS.INTL("06-Jun-14","09-Jun-14","1111101")

Haseeb, yeah only a few seconds apart :) In Excel 2013 this is a good example for Flash Fill. After entering the first three values you want to return (abc,abc,ac) just press Ctrl+E to fill the others.

Hi Shriva, nice formulas. I think the lookup value should be greater than any value in the lookup range for this type of approach to be robust so changing the lookup value from 1 to 2 could also fix that issue. Another possibility is: =LEFT(A2,MATCH(2,INDEX(1/(MID(A2,ROW($A$1:$A$99),1)="...

Another possibility... =PERCENTILE(B$2:B$6,1-PERCENTRANK(A$2:A$6,C2,20)) lookup value: C2. lookup range: A2:B6. Note: assumes decreasing data. (If data is increasing remove the "1-")

Very true @SirJB7 - i'd be the first to admit that. But it can be fun to think up ways to do it! (@NARAYANK991 gave a great explanation but it's really just a couple of tricks not art.) Fwiw, I think the shortest would be to combine the two methods above and use the 4x4 matrix method in the...

You sure that's what you get with your settings? I'm getting the opposite boolean values for your two examples with both formulas and find they agree with other formulas. Two top most notable members on this site suggest at least the first one is working for them. I haven't checked that...

... or this entered normally (also allows for leading zeroes): =MOD(SUM(-TEXT(LEFT(RIGHT(0&A1,{1,2,3,4,5,6,7,8}*2-{1;0}))*{1;2},"0 0\/1")),10)=0

Maybe this for 15 digit number? (checks the sum including the last digit is divisible by 10) =MOD(SUM(-TEXT(MID(TEXT(A1,REPT(0,16)),{1,2,3,4}+{0;4;8;12},1)*{2,1,2,1},"0 0\/1")),10)=0

=SUM((LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},"")))*{1,2,3,4,5,6,7,8,9})

Interesting point of view, i suppose much of the experience built through everyday work could be thought of as inductive chain learning. The way i see it, reading reference material online or signing up for courses (deductive learning) is important but will only get you so far in programming and...

actually for the data in question something like this might be sufficient: =MOD(SMALL(1000*ROW(A)+A,COLUMNS(A)*ROW(A)),1000) where data starts in row 1. For array data, ROW(A) could be changed to ROW(OFFSET(A:A,,,ROWS(A))) or a TREND formula similar to above. (This kind of approach should be...

Hi Sajan, i know this is a very late response but the original problem of finding row-wise max values based on a rectangular array A is an interesting challenge and still appears unresolved (i think you mentioned it elsewhere which led me here.) Haseeb's response does the job when A refers to a...

Sajan, i guess you're right that ranges beginning with Holidays should not be counted since these depend on leave days outside the range. For my suggestion i think one way to handle this would be to make the following insertion... replace:=1...

Hi I'm Lori, I work as a tactical developer and owe much to the online community for solutions that i've picked up and adapted over the years. No matter what your level of experience there's always lots more to learn and discover!

This was a tough one - even after a few days i'm struggling to figure out Sajan's formula, the idea to reverse LOOKUP with negative values is nice. Not sure it's much simpler, but for the record, here's my attempt... This basically checks if there is nothing other than Holidays and Sundays...

Hui - many congrats on MVP status! Nice work on the animated pendulums workbook which is awesome. There was also a related discussion with Doug Jenkins about a Hypercube animation constructed using only named formulas. Daniel Ferry has many other amazing examples of this technique at Excel Hero...

A key to many Excel formulas is the ability to return an array of integers {1;2;...;n}. One of my hopes with each new release has been that MS would include a function that did this for you like =INTARRAY(n). It's easy to write such a function in VBA but then you may have to worry about...

If you wanted to return the results as a single array, you could try this formula I posted recently from formula challenge 11. First define "m" to refer to the range B2:F9: Then select the range J2:J11 and enter the following formula using CTRL+SHIFT+ENTER...