• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 65
  • Last Modified:

Creating a Value in an Array

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?

Thank you in advance,

Bright01
C--Data-Temp-Finding-the-Frequency.xls
0
Bright01
Asked:
Bright01
  • 2
1 Solution
 
Farzad AkbarnejadCommented:
Hi,
Write the following formula in D4

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

-FA
0
 
Glenn RayExcel VBA DeveloperCommented:
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
 
Bright01Author Commented:
Glenn, Perfect.  Thank you very much!

B.
0
 
Glenn RayExcel VBA DeveloperCommented:
You're welcome.

-Glenn
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now