WhichQuestions
New Member
- Joined
- Mar 24, 2014
- Messages
- 2
[h=2]Using LINEST function with conditions[/h]
How would I apply conditions to data that crosses thresholds. I want to include only the cells that have satisfied condition of n>30 threshold in the LINEST formula. So after each "data sample refresh" I may have to expand or decrease the range used in the LINEST function.
Ideally, I would have one array selected for the LINEST and I would not have to manually increase/decrease the cells used for the function as my column thresholds are crossed.
n = No of vehicles (if n>=30 then the repair $ amount is displayed)
Top row = mileage band
Repair = costs to repair in that mileage band
[TABLE="class: cms_table, width: 992"]
<tbody>[TR]
[TD="width: 128"][/TD]
[TD="class: cms_table_xl65, width: 188"]LINEST Example: Repair
[/TD]
[TD="class: cms_table_xl66, width: 228"]=LINEST(C7:H7,$C$2:$H$2^{1;2},,TRUE)[/TD]
[TD="class: cms_table_xl66, width: 208"][/TD]
[TD="class: cms_table_xl66, width: 240"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: cms_table, width: 948"]
<tbody>[TR]
[TD][/TD]
[TD]50000[/TD]
[TD]100000[/TD]
[TD]150000[/TD]
[TD]200000[/TD]
[TD]250000[/TD]
[TD]300000[/TD]
[TD]350000[/TD]
[TD]400000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]50k[/TD]
[TD]50-100k[/TD]
[TD]100-150k[/TD]
[TD]150-200k[/TD]
[TD]200-250k[/TD]
[TD]250-300k[/TD]
[TD]300-350k[/TD]
[TD]350-400k[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Repair[/TD]
[TD="align: right"]$1,198.54[/TD]
[TD="align: right"]$1,054.26[/TD]
[TD="align: right"]$1,352.07[/TD]
[TD="align: right"]$1,767.28[/TD]
[TD="align: right"]$1,271.92[/TD]
[TD="align: right"]$1,722.23[/TD]
[TD]TOO FEW[/TD]
[TD]TOO FEW[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Ideally, I would have one array selected for the LINEST and I would not have to manually increase/decrease the cells used for the function as my column thresholds are crossed.
n = No of vehicles (if n>=30 then the repair $ amount is displayed)
Top row = mileage band
Repair = costs to repair in that mileage band
[TABLE="class: cms_table, width: 992"]
<tbody>[TR]
[TD="width: 128"][/TD]
[TD="class: cms_table_xl65, width: 188"]LINEST Example: Repair
[/TD]
[TD="class: cms_table_xl66, width: 228"]=LINEST(C7:H7,$C$2:$H$2^{1;2},,TRUE)[/TD]
[TD="class: cms_table_xl66, width: 208"][/TD]
[TD="class: cms_table_xl66, width: 240"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: cms_table, width: 948"]
<tbody>[TR]
[TD][/TD]
[TD]50000[/TD]
[TD]100000[/TD]
[TD]150000[/TD]
[TD]200000[/TD]
[TD]250000[/TD]
[TD]300000[/TD]
[TD]350000[/TD]
[TD]400000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]50k[/TD]
[TD]50-100k[/TD]
[TD]100-150k[/TD]
[TD]150-200k[/TD]
[TD]200-250k[/TD]
[TD]250-300k[/TD]
[TD]300-350k[/TD]
[TD]350-400k[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Repair[/TD]
[TD="align: right"]$1,198.54[/TD]
[TD="align: right"]$1,054.26[/TD]
[TD="align: right"]$1,352.07[/TD]
[TD="align: right"]$1,767.28[/TD]
[TD="align: right"]$1,271.92[/TD]
[TD="align: right"]$1,722.23[/TD]
[TD]TOO FEW[/TD]
[TD]TOO FEW[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]