# Posts by NYmerc

• ## Running Total With Declining Value

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

• ## Distributing Inventory into List

I have a list of inventory such as the sample attached and below - I need a formula that will distribute the quantities evenly (in order) with each fruit dropping off the list until the list is exhausted.

Can something like that exist?

• ## Calendar Conditional Formatting - Only Highlight Second Instance

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

• ## Calendar Conditional Formatting - Only Highlight Second Instance

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

• ## Time Math

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.

• ## Aggregate Function Issues

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

• ## Aggregate Function Issues

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)

• ## Aggregate Function Issues

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.

• ## Edit a Custom Number Format

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

• ## Drag Every Nth Column, with varying starting column by Date

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?

• ## Drag Every Nth Column, with varying starting column by Date

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

• ## Drag Every Nth Column, with varying starting column by Date

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.

• ## Changing Operator to gain a different result

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.

• ## Changing Operator to gain a different result

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

• ## Swap/Change Operator

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.

• ## Changing Operator to gain a different result

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.

Thanks!

• ## Sumproduct Dates

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

• ## Sumproduct with Year

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.

• ## Sumproduct with Year

Hi again,

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

Hope this will help