Posts by NYmerc


    As always Thank you Carim!


    Please see the attached updated sample file - the goal is to use sumproduct to identify the correct widget (in a much longer list in the final model) reconcile the year end cash flows by dates (not sum the widgets)


    Thank you again in advance!


    As always Thank you Carim!


    Please see the attached updated sample file - the goal is to use sumproduct to identify the correct widget (in a much longer list in the final model) reconcile the year end cash flows by dates (not sum the widgets)


    Thank you again in advance!

    This is really a two part problem - see attached sample. I need to solve the year/value error issue, but I also need to figure out how to use sumproduct to reconcile a calendar year. Cash flows bump up at a midway point in the year, so I need to divide the lower cash flow allocated to the first "half" of the year (not a true half, just the portion of the year that precedes the bump up date), and the latter "half" that includes the bumps, for a full year reconciliation.


    Any help would be truly appreciated.

    I have a column with years, blanks, and formulaic blanks (if false, than ""). I am running a sumproduct to identify a cash flow tied to a matching year. because some of the cells are not a serial number (or date), I am #Value!. Any ideas for how to resolve this error (I already tried iferror, but it makes the whole "Year()" false)...

    Carim, you have been a phenomenal resource, and I appreciate your assistance. I have made a modification to the "brute force" solution, and increased the "discount" premium to $7,000,000 in order to illustrate the way it should work. Your solution was correct based on my error in explaining the way it works... When trying the higher progressive premium, it ceases to kick out the right answer in either solution, and it still needs a "zero" bottom premium in the bracket.


    Thank you, that works great!


    Thank you, that is the same Sumproduct formula, I had tried, I see the difference is, that you added the "differential" in the rates in order to achieve right result - Thanks!


    Any idea on how to achieve (better yet integrate) the "discount" vs "New" Rate? The "New" in the second formula is the tricky one, since the start of the progression moves into different brackets based on how high up the stack the top of the discount completes (I brute forced the right answer in the newly updated version of the sample, to show what I mean)

    I have seen some progressive tax model online (this is not for a tax analysis but similar structure) using Sumproduct, but when I have tried the "googled" methods they don't produce the correct result, as was designed below - is there a way to simplify my formula using sumproduct (or some other alternative). I have attached a sample.


    =MAX(((MIN($K$7,$E9)-$C9)*$G$9+MAX(0,MIN($K$7,$E10)-$C10)*$G$10+MAX(0,MIN($K$7,$E11)-$C11)*$G$11+MAX(0,MIN($K$7,$E12)-$C12)*$G$12+MAX(0,MIN($K$7,$E13)-$C13)*$G$13+MAX(0,$K$7-$C14)*$G$14),$G$8)


    There is a new factor I need to insert for which I am having difficulty even fitting it into my existing formula - In the sample the "Total Premium" figure is $5,000,000; however, in practice a previously paid premium is offered a discount (so for instance if in the past $3,000,000 had already been covered, to replace the $3,000,000 with $5,000,000 the difference i.e. $2,000,000 would pay the "new premium rate" and the first $3,000,000 would pay the "discounted rate," starting, and continuing to progress from a higher bracket $1 - $5,000,000 bracket)...


    Any help would be very much appreciated

    Thanks, but how should I adjust the formula in order to generate the correct result - the latter have of the formula (the False in the iferror) is a weighted average growth formula. The goal is to have the formula automate when data is available (historical P&L figures) and when it is projecting...

    The formula below works - it "spits out" F26 as text. But when the Indirect is added, it becomes zero (instead the dollar figure in F26). Is there an alternative to indirect for converting text to a cell reference, or is there something I can do to the formula to remove the circular reference?

    =INDIRECT(CONCATENATE(SUBSTITUTE(ADDRESS(1,SUMPRODUCT(--(YEAR(Operating!$A$6:$P$6)=YEAR(CLSDate))*(COLUMN(Operating!$A$5:$P$5))),4),"1",""),SUBSTITUTE(ADDRESS(COUNTBLANK(Operating!$R$1:$R26),1,4),"A","")))


    thanks in advance.

    I need to figure how to rank a series based on it's distance to a "subject number," I have attached a rudimentary spreadsheet showing the series and the number.
    The series has several "10"'s in it, with a number (8) that is less than 10 and several numbers greater than 10.


    The goal is to have 10 ranked 1st as that is the subject number, and the 8 ranked 2nd since it is the nearest number (regardless of higher or lower), and 15 next, etc. Based on distance...


    Is this achievable?

    Files

    Why do you say it "kind of works" and that it is a "workaround"? Did I miss/ignore something?


    No not at all... Just in a greater context, of the workbook which is 10 worksheets, and sheet with the "switches" has 40 columns with unique input data, and for what needs to be extrapolated from this formula, its not really what I was going for... But I can make it work. Ironically, this sumproduct effort started as an need to replace an nested index/match with something a bit less "bulky" and more elegant (yours is certainly more elegant than how it started out)