Hi,
I recently replaced someone at work and I'm trying to improve his spreadsheet but have no clue on how he derived his very complicated formula. This is ultra complicated to me.
=IF(F44<$D$10,0,IF(AND(F44>=$W$18,M44="Behavioural Term"),VLOOKUP(H44,INDIRECT(K44&"!$B$128:$CK$249"),MATCH('1 | OBU FTP Calibration'!F44,INDIRECT(K44&"!$B$128:$XFD$128"),1),1),VLOOKUP(H44,INDIRECT(K44&"!$B$3:$CK$124"),MATCH('1 | OBU FTP Calibration'!F44,INDIRECT(K44&"!$B$3:$XFD$3"),1),1)))
[TABLE="width: 500"]
<tbody>[TR]
[TD]01/07/2012 (D10)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Start Date[/TD]
[TD]Months[/TD]
[TD]Currency[/TD]
[TD]Treatment[/TD]
[TD]Test[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10/12/2015 (F44)[/TD]
[TD]43 (H44)[/TD]
[TD]AUD (K44)[/TD]
[TD]Behavioural (M44)[/TD]
[TD]0% (W18)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
*I have 2 treatments, contractual, behavioural
*Different currencies are located in different tabs so tab 1 would have USD, tab 2 EUR, tab 3 JPY etc
*I would like to remove "Test" from the formula
Aim:
Currencies have two types of rates denoted in % for behavioural and contractual. Each rate is denoted % and categorised in months from 0 to 120 months (incremental of 1). Example: using the table above, AUD would have a rate of 1.02% for a 43 month denoted as 42.5 & 43.5
Please help - I need to come up with a formula that allows me to pull the correct rates based on the treatment & months & currency.
Thanks
I recently replaced someone at work and I'm trying to improve his spreadsheet but have no clue on how he derived his very complicated formula. This is ultra complicated to me.
=IF(F44<$D$10,0,IF(AND(F44>=$W$18,M44="Behavioural Term"),VLOOKUP(H44,INDIRECT(K44&"!$B$128:$CK$249"),MATCH('1 | OBU FTP Calibration'!F44,INDIRECT(K44&"!$B$128:$XFD$128"),1),1),VLOOKUP(H44,INDIRECT(K44&"!$B$3:$CK$124"),MATCH('1 | OBU FTP Calibration'!F44,INDIRECT(K44&"!$B$3:$XFD$3"),1),1)))
[TABLE="width: 500"]
<tbody>[TR]
[TD]01/07/2012 (D10)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Start Date[/TD]
[TD]Months[/TD]
[TD]Currency[/TD]
[TD]Treatment[/TD]
[TD]Test[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10/12/2015 (F44)[/TD]
[TD]43 (H44)[/TD]
[TD]AUD (K44)[/TD]
[TD]Behavioural (M44)[/TD]
[TD]0% (W18)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
*I have 2 treatments, contractual, behavioural
*Different currencies are located in different tabs so tab 1 would have USD, tab 2 EUR, tab 3 JPY etc
*I would like to remove "Test" from the formula
Aim:
Currencies have two types of rates denoted in % for behavioural and contractual. Each rate is denoted % and categorised in months from 0 to 120 months (incremental of 1). Example: using the table above, AUD would have a rate of 1.02% for a 43 month denoted as 42.5 & 43.5
Please help - I need to come up with a formula that allows me to pull the correct rates based on the treatment & months & currency.
Thanks