Use different formulas based on table values

[wysie218]

Hi, how do I look up values in a table, and based on the value, use a certain formula? For example, a student gets a refund based on how long they were in school before dropping...less than 3 days, 95% refund, 4-7 days(1 week), 90% refund,a quarter into the academic year, 75% refund, halfway through, 50% refund, and over halfway through, no refunds. I'm calculating the length of time in school based on their start date and last day attended (LDA), and the refund would be total tuition times percentage of refund due less any tuition paid so far. I thought building a table showing the refund structure would do it, and then just use IF statements to look it up, but does not seem to be working right. Any suggestions? Thanks.

 

[Faseeh]

Hi wysie218,


See this workout:


http://dl.dropbox.com/u/60644346/Fee%20Discount.xlsx


Regards,

Faseeh

 

[Luke M]

If you've got the refund table built like this

[pre]

Days   Refund
0        95%
4        90%
8        75%
30       50%
60       0%

[/pre]
Then you can just use a LOOKUP formula to find the correct refund amount, ala

=LOOKUP(DaysInSchool,RefundTable)

 

[wysie218]

Thanks Faseeh...vlookup works so much better than an IF statement. Much appreciated.

 

[SirJB7]

Hi, wysie218!


Let's say your table ranges thru columns A:D in sheet Sheet1, where column A is the key for searching and column C has the parameter for the formula.

In sheet Sheet2, you have in column A the value to be searched, and you want to retrieve in column B the parameter to be used in the formula in column C.

Type this:

B2 : =VLOOKUP(A2,Sheet1!A:D,3,FALSE)

C2 : =IF(B2<=3,.95,IF(B2<=7,.9,IF(B2<=90,.75,IF(B2<=180,.5,0))))*value


Regards!

 

[wysie218]

Wow, learned something new. Thanks Luke M and SirJB7.

 

[SirJB7]

Hi, wysie218!

Glad you help you. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!