# Posts by TheDude

• ## Lookup values in an array and return the offsetting lowest number

Re: Lookup values in an array and return the offsetting lowest number

Pike you never cease to amaze me....that is awesome. I thought small might be the answer but I wasn't sure how to build the formula. So in the argument for "K" you have countif with a blank as the criteria then you add 1, how does that work? I take it that it counts the number of values in the array that match the criteria of X.

Thanks,

-Dude

• ## Lookup values in an array and return the offsetting lowest number

I am trying to look up values in an array and return the offsetting lowest number. For example I show a sheet with Rows 1 - 3 and columns A-C I would think that an array formula that is {=MIN((A2:C2="X")*(\$A\$1:\$C\$1))} would work as it works for the Max but i cant get it to work Any ideas? I have attached a sample sheet if it helps.

PHP
``````A  B  C
1  5  3  6
2  X  X
3  X     X``````

Row 2's value would return 3, Row 3's value would return 5

Thanks,

-Dude

• ## Automating a water fall XIRR return in excel

Re: Automating a water fall XIRR return in excel

I have attached a revised model that shows quarterly timing. I am also revising the explanation.

To automate a waterfall return you simply use the NPV of the current period and the NPV of the previous period to find out when the current value is positive and the preceding value is negative. Since the discount rate is essentially the cost of capital you will not have a positive Net Present Value until you reach the targeted IRR.

Using the IF(and()) formula you will plug in the And() formula for both the NPV of the current period total cash flow as well as the NPV of the previous periods NPV using the ">=" and "<=" arguments. This results in finding the correct period to plug the correct value to hit the targeted return.

Once you have found that period the FV or Future Value formula plugs the right amount to hit the targeted IRR or Internal Rate of Return. Should you use the NPV formula with the correct plug for the targeted return from the FV formula then you would have a zero NPV.

Thanks,
-Dude

• ## Automating a water fall XIRR return in excel

Re: Automating a water fall XIRR return in excel

Special thanks to Globaliconnect

This uses the "FV" formula combined with the "NPV" or "XNPV" formula to automate a cash flow plug for a targeted IRR or XIRR return. You also need to convert your hurdle rate into monthly or quarterly increments (depending upon your model) to use as the "rate" in both the FV and NPV formula and then create an if statement to apply the solution.

Thanks!
-Dude

• ## Automating a water fall XIRR return in excel

Sorry for the cryptic question I am not sure how to word this...

I have attached an example of a typical real estate deal. In the example scenario I show an investment and a stream of cash flows. The investor covers the equity and receives 100% of the cash flow until he reaches an 18% XIRR, after that point he only receives 80% of the cash flow until he reaches a 20% XIRR. Once both preferences are met the investor receives just 50% of the cash flow.

The real question I have is....how do you automate target XIRR returns with a formula?
The excel question i have is....Is there a way to use goal seek within a formula?

Thanks,

-Dude

• ## Index Match Problem

Re: Index Match Problem

Try incorporating a Dmin formula or a Dmax formula. Database formulas take way less resources than array formulas.

-Dude

• ## Column to display variance of X number of cells selected randomly from another column

Re: Column to display variance of X number of cells selected randomly from another co

you could use a combination of the INDEX formula with the RANDBETWEEN formula. Index the array where your data is stored and for the row number use the randbetween with the min value of 1 and the max value being the total rows in your data set. This would give you a single random value, just copy this procedure for as many values that you need to average and once you have created your random value range you can then average the range.

• ## Calculating a Four Week Rolling Average

Re: Calculating a Four Week Rolling Average

No prob

• ## Calculating a Four Week Rolling Average

Re: Calculating a Four Week Rolling Average

With the little information I know from the need you have, you should be able to use the "Daverage" formula. It is a part of the database formulas. See the example i posted.

-Dude

• ## countif and vlookup

Re: countif and vlookup

See the attached sheet. I didnt have time to put it in a separate sheet and do the vlookup you requested but it will give you an idea of one way out of many to do what you are wanting to do. I used the countifs function.

Best Regards,

-Dude

• ## Sumproduct Syntax

Re: Sumproduct Syntax

Can you post a sample worksheet?

• ## Multiply an "IF" formula to find a new total without changing the entire formula

Re: Multiply an &quot;IF&quot; formula to find a new total without changing the entire formula

Cant you just multiply your accommodation by 8?

• ## Multiply an "IF" formula to find a new total without changing the entire formula

Re: Multiply an &quot;IF&quot; formula to find a new total without changing the entire formula

What are you trying to do?

• ## Complex Filter with Multiple Critera and Multiple Values

Re: Complex Filter with Multiple Critera and Multiple Values

Yeah,

Try Power Pivot, its a Microsoft add-in that allows you to create a data cube within excel and do advanced pivot tables. Also, it allows you to convert the pivot table output to formulas so you can format the data to any custom report. Its also free.

http://www.microsoft.com/en-us/bi/powerpivot.aspx

-Dude

Perhaps a quick solution, have you considered the 64bit version of office? This allows you to utilize more than 2g of ram.

-Dude

• ## Use Database formulas (Dcounta) with multiple rows as the criteria

Re: Use Database formulas (Dcounta) with multiple rows as the criteria

Hey,

I have found a solution that works the best for my situation. I have used the index match to adjust the dynamic criteria range of the DSUM and DCOUNTA. If this set up properly, you can copy and paste for all of your data and have no problems with calculations. This is primarily useful in merging inconsistent databases while keeping the data in its raw format.

-Dude

• ## Use Database formulas (Dcounta) with multiple rows as the criteria

Re: Use Database formulas (Dcounta) with multiple rows as the criteria

Is it possible to combine two ranges into one range? Could I have the criteria-field-names as a static range and the criteria-constraints as a dynamic range? then the combination of the two would be the criteria. Is this possible?

Thanks,
-Dude

• ## Use Database formulas (Dcounta) with multiple rows as the criteria

Re: Use Database formulas (Dcounta) with multiple rows as the criteria

Thanks AAE,

Unfortunately that wont work for me. I am trying to translate another database into my dashboard database so that i can run my reports off of the data. I like the pivot table but i was not able to get the dates in order. For this report the first day of each week starts on a Monday and the last is a Sunday. I'm going to keep hammering away at the offset function and see if I can get that in order.

Thanks!
-Dude

• ## Use Database formulas (Dcounta) with multiple rows as the criteria

Hey,

I am trying to use the database formulas DCOUNTA and DSUM with multiple rows as the criteria. Syntax being: DCOUNTA(database, field, criteria) where the criteria is an expanding range. Normally you can simply have the following DSUM or DCOUNTA delete the preceding values and then arrive at an accurate figure (shown in the example). However, this increase the calculations and size of the spreadsheet dramatically...thus the need for an alternative solution. I have read of several examples using the offset formula to fix this problem, however due to my inexperience I have not been able to implement that solution. Any help or other solutions would be appreciated.

Thanks,
-D

• ## count of consecutive numbers in a row

Re: count of consecutive numbers in a row

From the above formula i modified to =IF(OR(AND(A2=-1,A3=-1),AND(A2=-1,A3=0)),1+B1,0) to capture the last one that did not have a number then use the formula =max(B:B) to find the highest value assume column B is the formula row. See the attached sample.

-Dude