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
-
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.
-
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
Hope this will help
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)?
-
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...
-
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"