Posts by NYmerc

    Hi - I have attached a sample of what I am trying to accomplish. I have a value(s) that needs to decline over a period of time, essentially I want to be able to change the number of years it takes to see a balance hit zero on the table, while also being able to start the process again every year.

    In the sample in 2022, There is a starting balance of $1,000,000 declining by 33% (aka 3 years), year 2 has no balance but year 3 has a residual from year 1 but starts a new declining balance of $1,200,000 (totaling $1,533,333.33). Is this doable without a macro?

    Any help would be appreciated


    I am building a calendar into a spreadsheet - and like most calendars, because a month may not start end on Monday/Sunday, there are end of the previous/next month listed.

    If I were trying to use Conditional Formatting to highlight a date, but the date appears twice (once at the end of the previous month, and again in the appropriate month), how would I only highlight the second instance?

    Thank you in advance - any assistance would be appreciated

    I have a series of multiple train routes that come to the same terminus, before a transfer. I am trying to calculate total travel duration. Since every train arrives at a different time - utilizing one example, If I have a train arrival time of 8:37, and my connection is at every ten minutes on the 0:04 (meaning 8:24; 8:34; 8:44, etc), how would I automatically calculate the time difference, if I were trying to add the wait time to total duration of my trip?

    The minute function calculates all the means (in the 10's column and the 1's column, so I can't do a min/max to determine which to subtract from)

    Any help would be appreciated.

    It wasn't the solution I was looking for but =SUMPRODUCT(SUBTOTAL(9,OFFSET($Y$8,ROW($Y$8:$Y$11)-ROW($Y$8),0)),($X$8:$X$11="Ties")+0) worked for what I was attempting to use aggregate for.


    I have attached a sample workbook - I am trying to use the aggregate function instead of Subtotal, because I have criteria:

    I have tried several iterations of =AGGREGATE(9,3,Sales1*(Prod1="Ties"))

    I am relatively new to the use of the function, it would be greatly appreciated if someone could help me solve the problem but also explain what I am doing wrong for future use.

    Is it possible to "edit" a custom number format? Excel limits the maximum number of custom number formats, if I made a spelling error, and all I want to do is edit the format, can this be done. The reason I want to edit instead of deleting, is because custom number formatting is quirky, and when I delete a format, it shifts other custom formats within the workbook to a different saved custom number format (I would save a sample workbook, but not sure how to save a "deleted" number format).

    Any help would be appreciated

    I have attached a sample worksheet, the formula below assists in determining the starting cell.


    The goal is to use a formula similar to: =OFFSET($C$5,0,(COLUMN(A8)*3)-1) and drag a result (in this case "$2,500) from the starting cell, and every five years thereafter. Any assistance would be appreciated.

    Thanks Pike, I appreciate your efforts, but that’s not the solution I’m looking for. The non-sample formula currently reads: =if(h4=%,five-line-formula-that-results-in-$1000,000*(1+H15),five-line-formula-that-results-in-$1000,000+(H15*squarefeet)).
    ideally,I wouldn’t have to repeat the five line formula if the false position.

    Thanks, I would do that, but it defeats the purpose of using choose. The sample worksheet assumes that H5 is a simple plugged number. In the actual spreadsheet it's a long formula that I'd rather not have to repeat in order to achieve two different results based on a switch...

    Attached is a sample worksheet. I need a method that can help me choose between multiplication and addition (i.e. x*(1+2%) or x+0.5, but the 2% and 0.5 would be in the same cell and would otherwise be dependent on a different formula acting as a switch). Any ideas would be greatly appreciated, I have already attempted to use the "Choose" function as you can see in the sample.


    If in a series of dates and corresponding cash flows (say: 12/31/2018 - 12/31/2033) you want to use sumproduct to return the cash flow for a date not in the series (say: 12/31/2016), can sumproduct return the cash flow ties to 12/31/2018 (oldest and therefore closest date)?

    To be quite honest with you ... this is indeed quite confusing ... :confused:

    However, in order to give it a try ... to get closer to your goal ...

    would you mind attaching an updated file which would precisely reflect your differentiated costs increases ... :wink:

    Carim, I always appreciate your help (and I have learned a lot from you) I just thought I would share the solution, that I got to work. Please see the attached.

    Hi again,

    Attached is a proposal - Version 2 - in order to dynamically generate your Results Table ...

    Hope this will help

    Thanks for your help!

    But unfortunately, this is similar to what I already had. The reason, I need it to be more flexible is, say a widget's cost increase is not grown by percentage; but rather per unit (i.e. rather than dollar*(1+X.XX%)^y-periods. We have Quantity*$X.XX=dollar+(Quantity* $x.xx^Y-periods. The Y-period needs to be able to non-consistent/systematic, so the first period might be five years, but the second or third might be 3 or 1 or 7, etc). At the end of the day, what I really need is a way to lookup the widget and the year, and apply the yearfrac, since the month is not likely to change, only the year and period...

    Sorry if this is confusing...