Posts by NYmerc

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.

Quote from Carim

Hello,

Thanks for your sample file ...

However, could you expand a little ... and provide a detailed layout of all the Cash Flows numbers related to the "first half" of the year ...

as well as the Number you are expecting as the final result generated by your Sumproduct formula ...

Cheers

Display More

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!

Quote from Carim

Hello,

Thanks for your sample file ...

However, could you expand a little ... and provide a detailed layout of all the Cash Flows numbers related to the "first half" of the year ...

as well as the Number you are expecting as the final result generated by your Sumproduct formula ...

Cheers

Display More

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.