Need a Formula where the Search Field on the Array is referenced/dependent on a Cell Value

[Dantexz]

Hi,

I would like to know if it is possible to have a formula where the search field of the array is dependent on the number I put into a cell. As an example:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B [/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]7[/TD]
[TD]4.5[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]9[/TD]
[TD]5[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]6[/TD]
[TD]3
[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want to perform a calculation on the above A1 to B4 that would give an answer that is less than the number I put in C.

So in D1 I would like the formula to calculate the the Max A1:A3 (which is 9) minus Min B1:B3 (which is 3), as this would give an answer that is less than 7 (C1).

In D2 I would like the formula to calculate the Max A1:A2 (which is 9) minus Min B1:B2 (which is 4.5), as this would give an answer that is less than 5 (C2).

In D2 I would like the formula to calculate the Max A1:A4 (which is 9) minus Min B1:B4 (which is 0), as this would give an answer that is less than 10 (C3).

Thanks!

Regards,

Peter

 

[baitmaster]

Welcome to the board. I can't grasp entirely what you're trying to do here. As a rule you can't normally change the inputs in order to suit the answer - generally you would fix the input, and then select which values you pull from that range according to some criteria. In your case I can't see fully what the criteria are, for example in all of the options above, why aren't all of the formulas simply [Max A1:A1 - Min B1:B1] = 2.5? This meets your stated criteria, so what else are you trying to achieve?

There is one suggestion that you might find of use, if you can explain what is driving the change of input range. =OFFSET(start cell, rows, columns, height, width) allows you to vary the range that you are looking at, although you wouldn't be able to see the actual range itself unless you embedded this formula into a name, i.e. created a named formula (it's like a named range, but not a range if that makes sense)

 

[Dantexz]

I'm trying to use Excel to help with share trading. My last post didn't explain things too well. Let present the table again with appropriate labels:

<table cellspacing="0" cellpadding="0" dir="ltr" border="1" fixed;="" font-family:="" arial,="" sans,="" sans-serif;="" border-collapse:="" collapse;="" border:="" 1px="" solid="" rgb(204,="" 204,="" 204);"=""><colgroup><col style="width: 128px;"><col width="121"><col width="114"><col width="97"><col width="120"></colgroup><tbody>[TR]
[TD][/TD]
[TD="bgcolor: #FAFAFA"]A Max Price ($)[/TD]
[TD="bgcolor: #FAFAFA"]B Min Price ($)[/TD]
[TD="bgcolor: #FAFAFA"]C Range ($)[/TD]
[TD="bgcolor: #FAFAFA"]D Days in Market[/TD]
[/TR]
[TR]
[TD="bgcolor: #FAFAFA"]Day 01[/TD]
[TD="bgcolor: #FAFAFA"]7[/TD]
[TD="bgcolor: #FAFAFA"]4.5[/TD]
[TD="bgcolor: #FAFAFA"]7[/TD]
[TD="bgcolor: #FAFAFA"]3 (Days 01-03)[/TD]
[/TR]
[TR]
[TD="bgcolor: #FAFAFA"]Day 02[/TD]
[TD="bgcolor: #FAFAFA"]9[/TD]
[TD="bgcolor: #FAFAFA"]5[/TD]
[TD="bgcolor: #FAFAFA"]5[/TD]
[TD="bgcolor: #FAFAFA"]1 (Days 02-03)[/TD]
[/TR]
[TR]
[TD="bgcolor: #FAFAFA"]Day 03[/TD]
[TD="bgcolor: #FAFAFA"]6[/TD]
[TD="bgcolor: #FAFAFA"]3[/TD]
[TD="bgcolor: #FAFAFA"]10[/TD]
[TD="bgcolor: #FAFAFA"]3 (Days 03-05)[/TD]
[/TR]
[TR]
[TD="bgcolor: #FAFAFA"]Day 04[/TD]
[TD="bgcolor: #FAFAFA"]8[/TD]
[TD="bgcolor: #FAFAFA"]0[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[/TR]
[TR]
[TD="bgcolor: #FAFAFA"]Day 05[/TD]
[TD="bgcolor: #FAFAFA"]7[/TD]
[TD="bgcolor: #FAFAFA"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #FAFAFA"]Day 05[/TD]
[TD="bgcolor: #FAFAFA"]11[/TD]
[TD="bgcolor: #FAFAFA"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody></table>
Column A is the maximum traded price of the day.
Column B is the minimum traded price of the day.
Column C is range of the price difference I can tolerate to stay in the market. This varies because I'm trying to test how much money to put in to get the maximum returns (I've got a different spread sheet to calculate the returns, I only need this one to tell me how many days the money I put in will keep me in the market). The more money I put in the more days I'm able to stay in the market, but the less return I get as a %.
Rows are the different days.
Column D is the information I need - how many days I'm able to stay in the market.

So basically I'm trying to find out (ie in D3): How many days I'm able to stay in the market (ie 2 days from Day 03), based on the amount of money I put in (ie $10).

Thanks for your help!

 

[Dantexz]

Sorry, don't know what happened to the last post. Here it is again.

I'm trying to use Excel to help with share trading. My last post didn't explain things too well. Let present the table again with appropriate labels:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A Max Price$[/TD]
[TD]B Min Price $[/TD]
[TD]C Range $[/TD]
[TD]D Days in Market[/TD]
[/TR]
[TR]
[TD]Day 01[/TD]
[TD]7[/TD]
[TD]4.5[/TD]
[TD]7[/TD]
[TD]3 (Days 01-03)[/TD]
[/TR]
[TR]
[TD]Day 02[/TD]
[TD]9[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]1 (Days 02-03)[/TD]
[/TR]
[TR]
[TD]Day 03[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]10[/TD]
[TD]3 (Days 03-05)[/TD]
[/TR]
[TR]
[TD]Day 04[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Day 05[/TD]
[TD]7[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Day 06[/TD]
[TD]11[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Column A is the maximum traded price of the day.
Column B is the minimum traded price of the day.
Column C is range of the price difference I can tolerate to stay in the market. This varies because I'm trying to test how much money to put in to get the maximum returns for different days (I've got a different spread sheet to calculate the returns, I only need this one to tell me how many days the money I put in will keep me in the market). The more money I put in the more days I'm able to stay in the market, but the less return I get as a %.
Rows are the different days.
Column D is the information I need - how many days I'm able to stay in the market.

So basically I'm trying to find out (ie in D3): How many days I'm able to stay in the market (ie 2 days from Day 03), based on the amount of money I put in (ie $10).

I hope I'm clearer with this post. Thanks for your help!!