Solved

Creating a Value in an Array

Posted on 2014-08-20
63 Views
EE Pros,

I'm working to produce a data table based on two variables; Time Horizon and Frequency.  Both are time variables.  You can see in the spreadsheet I provided that by selecting a Horizon and a Frequency, it returns a value that then corresponds to a table.  Here's my challenge, how do I use a lookup formula to determine the number to multiply with?  Once the number is determined in the table, it can be multiplied by the Time number (e.g. 3 months) to get a total # of frequency data points to derive (over the given Time Horizon).

Up for the challenge?

Bright01
C--Data-Temp-Finding-the-Frequency.xls
0
Question by:Bright01

LVL 14

Expert Comment

Hi,
Write the following formula in D4

=OFFSET(\$N\$5, E4,E3)

-FA
0

LVL 27

Accepted Solution

If you use the following formula, you won't need your "Results" values.  Additionally, this multiplies the found result in your matrix (O6:T11) by the value in D4 to get the correct number of Frequency data points.
=OFFSET(\$N\$5,MATCH(\$C\$4,\$M\$6:\$M\$11,0),MATCH(\$C\$3,\$O\$4:\$T\$4,0))*D3

Additionally, I found several issues with the ratios in the table to the right and have updated all the values where needed.

Regards,
-Glenn
EE-Data-Temp-Finding-the-Frequency.xls
0

Author Closing Comment

Glenn, Perfect.  Thank you very much!

B.
0

LVL 27

Expert Comment

You're welcome.

-Glenn
0

Featured Post

Suggested Solutions

offset an array formula 4 23
25th of every month 7 38
excel forecast function 1 27
IF OR ANd STATEMENT - EXCEL - TWO CONDITIONS 10 24
Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…