Formulas in Excel

[steve daddario]

Hey everyone,

Im currently trying to create a compensation plan in excel. My question is how do I set it up so that when a certain number is put into a cell that number will take into consideration the componets to calculate a new number using the components.

The compents are if the number is within 90-100% of target profit their is no reduction.
80-89%-10% reduction
70-79% 20%
below 70% 30% reduction.

Any ideas.

 

[dave3009]

Hi and welcome to MrExcel

Try this

=LOOKUP(H8, {-9E+255,0.7,0.8,0.9}, {0.3,0.2,0.1,0})

Where H8 contains the percentage of target profit achieved,

 

[steve daddario]

Can you explain using Excel 2003 because thats the software we have here.

 

[steve daddario]

Dave,
I cant seem to get that to work

 

[dave3009]

This is what I tried

Excel Workbook
	H	I
7	Profit Variance	Reduction
8	100%	0%
9	91%	0%
10	88%	10%
11	78%	20%
12	66%	30%
Sheet1

Excel 2007

Cell Formulas
Range	Formula
I8	=LOOKUP(H8, {-9E+255,0.7,0.8,0.9}, {0.3,0.2,0.1,0})

 

[steve daddario]

yah thats exactly what im looking to do but I cant get it to work in excel 2003

 

[steve daddario]

Got it. Little confusing with 2003 since im a excel 2007 person myself. Thanks for taking the time to do that.