Search results

Sajan, appreciate this formula being brought to light and good idea to get people to try different values for rate to get to grips with the NPV function. Other useful values for rate are 0, 1 and -2, a few examples with explanations are in the links below...

Hi Sajan, thanks for the positive feedback. Your formula works well for the single column case as described in the original challenge and is more efficient than mine. My intention had been to allow m to be a rectangular array (eg A2 : D9) hence the extra complications but maybe there's a simpler...

For distinct values from a range of one or more columns containing text or numbers, maybe (Office 2010/13)...

Hi Jeff - regarding OFFSET weird behaviour, i noticed you had quoted a couple of comments posted elsewhere further up this thread under my initials. I'm not sure about their validity but thanks! Following on from Sajan's previous comment about CONVERT, it turns out many other engineering...

Was going back over previous challenges and found some great ideas here and elsewhere. Maybe the MODE.MULT function could also be put to use for these type of problems. For distinct entries in the list: =LOOKUP(MODE.MULT(IF(MATCH(m,m,0)=(ROW(m)-MIN(ROW(m))+{1,1}),ROW(m))),ROW(m),m)

To round things off, here's a VBA user defined function adapted from Sajan's initial suggestion: Function MaxEntry(List As Range) Dim i, l, d, m, x With Application i = .Evaluate("row(" & List.Address(, , , 1) & ")") l = .Lookup(i, .Choose(.IsText(List), i)) d =...

Shriva (& Narayan), good job on the bonus question, i knew it wouldn't take long to get there. This was really a passing remark and should probably have been phrased more clearly. Sajan, i'm flattered but i think the posts you refer to are more scientific in nature like those on the...

Sajan - quick off the mark as usual, I think that's pretty optimal. My original formula (120 chars) is very close to this: =LOOKUP(1,1/FREQUENCY(0,IFERROR(1/SUBTOTAL(9,OFFSET(x,ROW(x)-MIN(ROW(x)),,FREQUENCY(-ROW(x),-ISTEXT(x)*ROW(x)))),-1)),x) Thanks to all for your contributions. Looking...

Sam, can you refine this idea? The result may be a few characters longer than the other way but is comparatively very efficient and could easily be extended to many thousands of rows. Hint: Try replacing the {2,3,2,1} array with my other suggestion above and {1,4,8,11} with ROW(x).

Sajan (& Sam) - Nice tweaks you definitely win on formula length now! I'll post my approach in a day or two... Narayank991 - That's a very valid point. I'd be happy to accept either approach (p or e in your example) providing it's consistent and meets the initial spec which those posted so far...

Sam - That seems to work well for the first row, but maybe a small adjustment is needed in case the list is located elsewhere. Here's some further clues relating to my suggestions above (to help save on costly real estate :)): a) FREQUENCY(-L,-y) b) FREQUENCY(-ROW(x),-ISTEXT(x)*ROW(x)) where L...

Sajan - That's a great solution and you got there much quicker than me. I see ideas from the previous challenge being implemented here. As you say, there are possibilities for further efficiency improvements. One way is to use the FREQUENCY function: a) to pick out the largest sum (which...

Thanks Haseeb, this was a great challenge. I have just posted a new one if anyone wants a go... Regards, Lori

Given a single column list containing text items with numbers following, find the item with the largest sum. For example in the following list your formula should return c (with a sum of 9). a 1 3 b 1 2 5 c 2 7 d 8 Your answer should be a single formula involving Excel functions only that...

=SUM(IF(LOOKUP(ROW(D),ROW(D)/(D<>""),D)="A",V)) [with Ctrl+Shift+Enter]