Search results

Simpler... in the sense of employing only five functions, Peter's approach is simpler in the sense of readability. And most would agree the simplest method to follow would be to use Hui's original set up with a data table attached - which is in effect a solution with helper cells though it may...

Had another look at this yesterday as was sure there was a simpler approach... =SUMPRODUCT(($C10:$C31+F6-MMULT(ABS(SUMIF($B10:$B31,{"<=","<"}&$B10:$B31,$C10:$C31)-SUMIF($F8:$Q8,{"<=","<"}&F8,$F6:$Q6)),{1;1}))^{1,2}^{1,0.5}/4*$D10:$D31)/F6

Interesting topic. From object browser, Range is a property that returns a range object whereas Evaluate (or [.]) is a method that returns a variant. Because of this each has pros and cons, according to the context in which they are applied... 1. Assignment. Since Range("a") is an object...

For a formula without inserting any extra rows, you could first select the range Transaction_Report!$A$1:$J$117 and choose: Formulas > Defined Names > Create From Selection > Check Top Row Only > OK. Then fill down this formula from I3 on the dashboard sheet (non-cse)...

As requested, here are some rough notes on the previous formula construction, maybe Hui can incorporate in a format suited to FF. In another post, Narayan has spoken of compiling formula patterns for solving problems. The key patterns needed here are arrays of running sums together with...

Late seeing the tag in this post. Interesting challenge and great solutions by JJV. One minor tweak might be to use EFFECT(.,1) in place of EXP(LN(.)) to minimise rounding issues. Fwiw, here's one more non-cse option referring only to constant cell values filled across (F38)...

The SEQ function has been suggested by another user with some additional arguments... https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10629924-sequence-function-numeric-run please vote up!

Hi peter, i have added a number of tricks to my collection from your demo files such as a self-referencing shuffle formula that I didn't believe even possible previously. The TREND suggestion returns an increasing sequence of numbers between 0 and 1.5. I posted on the vault forum some time back...

As per Hui's comments, a first step could be to restructure data into a tabular format more amenable to analysis by BI tools thereby avoiding the need for (clunky) dynamic references. A simple approach, based on the set up in the initial post, could be to define: blockID: =MATCH(rowNum...

Adding the ace and face cards could simplify it a little: =SUM(ISNUMBER(SEARCH({2,3,4,5,6,7,8,9,10,"J","Q","K","A"},I11))*{2,3,4,5,6,7,8,9,10,1,1,1,1}) (Also maybe an idea to use "T" for 10 so each card is represented by a single character)

I think for the particular setup you could try: =SUM((LEN(I11)-LEN(SUBSTITUTE(I11,{1,2,3,4,5,6,7,8,9,0},)))*{1,2,3,4,5,6,7,8,9,9})

A key feature worksheet functions lack are the equivalents of FILTER and DISTINCT functions for working with arrays. Like others my experience of DAX is very limited and I hope to learn more of these powerful features. Looking at the daxpatterns link, the cumulative quantity calculation is...

Without helper column, maybe: 1. Define the name 'List' to refer to the column of data (eg $H$2:$H$22) 2. Define the name 'SumBoldList' to refer to: =SUM(GET.CELL(20,IF(1,+OFFSET(List,ROW(List)-MIN(ROW(List)),)))*List) 3. Enter in a cell: =SumBoldList If you're just needing a quick result it...

..but it would be funny! Poor design can often arise due to the limitations inherent in spreadsheets for working with variable sized data on a fixed two dimensional grid. A number of commercial add-ins (quantlibXL, ManagedXLL, .. ) avoid this to some extent by allowing one to work with data...

Hi Peter, Nice simplification for the single digit case. And yes, these kinds of tasks would be much simpler if there were built-in operations for sorting or ranking by row or column - currently require long formulas similar to those found in some prior challenge threads. The comments relating...

Yes, numeric limits should be same - though last decimal place is rounded to 7 in langage references. Can see this from immediate window: [A1]=1.7976931348623158e+308 ?[A1].Value(11) (Others might know already but .value(11) is new to me in showing the extra digits in cell values) Another way...

Ok, I admit the question was a bit ambiguous. For me, all answers posted so far have some validity. Chihiro rightly points out limitations of using 15 significant figures as the result displayed is larger than the max value which needs 17 figures to enter as in the link of vletm. I think you...

@vletm - that link is correct but the question was a slightly trick one as it asked for the displayed number and was just asked out of curiosity. My answer is 3.6E+308 which is the display value of NaN on the sheet. Make of it what you will... (This value also has a use in charting as it...

@vletm: indeed that lists the largest standard number as a little greater than what shriva gave. However, the double precision format also allows for some other special values. One way to return the '+Infinity' value from VBA is: Function INF() as Double On Error Resume Next INF = 1 / 0 End...

That's the largest number that can be displayed in a cell that doesn't end with a zero. And most people know the largest number that can be entered as a constant is: 9.99999999999999E+307 I was looking for a way to return values greater than this.

What is the largest number that can be displayed in a cell? Seems like a simple enough question. But I for one was surprised by the answer - thanks to a Stackoverflow post I found recently.

Hi David, I agree that COUNTIF can sometimes give seemingly erratic results. Here's a related example: Format cells A1:A3 as text, and enter in these cells (in any order): 007 07 7 Now try the following three formulas: =COUNTIF(A1:A3,A1) =COUNTIF(A1:A3,"="&A1) =COUNTIF(A1:A3,"<="&A1) These...

You could try: =SUMPRODUCT(COMBIN(A1,ROW(INDIRECT(B1&":"&C1)))) A non-volatile alternative is: =BINOM.DIST.RANGE(A1,0.5,MIN(B1:C1),MAX(B1:C1))*2^A1

Hi David, Thanks, good challenge! A fully reliable solution was found eventually, based on the ideas of yours further up. Just to clarify what i said before... - in that first formula with CTRL+SHIFT+ENTER: replace '2' with 'PRODUCT(CHOOSE(H1:K1,1,2,3,5,7))' - in that second formula, try...

Previous suggestion was designed to work efficiently with sample data. For any numeric data (as well as for text), can try: =SUM(--(MMULT((COUNTIF(H1:K1,A1:D20)>0)*10^COUNTIF(H1:K1,"<="&A1:D20)-10^COUNTIF(H1:K1,"<="&H1:K1),{1;1;1;1})=0))