Posts by NYmerc

I need to amend my thread - I don't need the second instance, I need the instance that is connected to the correct month.

Hi,

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.

Thank

Thanks but the reason I am not using Sumif or Sumproduct, is I need it to operate more like subtotal (in that it skips the other subtotals, lower down)

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

Quote from pike

thanks for sharing

Thanks, my solution does have one unintended flaw - perhaps you can help still. When the number rises above 90, it doesn't switch to column AA. Any ideas how to make the solution perpetual?

Solved myself, by changing column letter using dates.

=IF($E$6>I$5,0,CHAR(CODE(IF(MOD(YEARFRAC($E6,I$5),$H$12)=0,$G$11,0))+IF($E$6>I$5,0,IF(MOD(YEARFRAC($E6,I$5),$H$12)=0,YEARFRAC($E6,I$5)))))

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

=INDIRECT(TEXT(SUBSTITUTE(ADDRESS(1,SUMPRODUCT(--(YEAR($E6)=YEAR($G$5:$O$5)),$G$4:$O$4)+COUNTBLANK($A$5:$F$5),4),1,""),0)&ROW($E6))

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.

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.

Quote from Carim

Hello,

Say your dates are listed in range A2:A17

in cell B2 =DATE(2016,12,31)

In order to get the closest date ...you could have in cell C2 the following Array Formula

Code

1. =MIN(IF(A2:A17>B2,A2:A17))

Hope this will help

Display More

Thanks!

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)?

Quote from Carim

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.

Quote from Carim

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...

Quote from Carim

Thanks for your updated file ... As soon as I have a moment ... I will dive into your spreadsheet ... :wink:

I just noticed an error in the steps description it should've said: "Steps 2 ===> Identify corresponding cash flow in column K, by "Year" reference in column F multiplied by its appropriate yearfrac"

Quote from Carim

Hello Daniel,

Sorry but I am still confused about your goal ...

What are exactly the steps (and the required calculations) to achieve your goal ... :duh:

Thanks Carim, as always your assistance is appreciated.

I have updated the same to include a "proforma" in a method I have used in the past that does work, but isn't flexible enough to achieve future goals.

I have also included the steps. Hopefully, this explains the goal.