If and statements

[darrenciccone]

Hello, I will say thank you in advance for all your help.

I am working with a bonus project and I have corresponding % rates (for a certain percentage increase of unit sales; so for example if customer A bought 100 widgets last year and then this year he buys 105 he falls into the 101%-105% increase in units bought category that extra 5 units qualifies for a $ bonus based upon dollar sales of the incremntal units and I want the formula to return the appropiate % on the incremental dollar amount; so that for a range 1%-5% it Mulitplies the Incremental dollar amount by 1% and moreover if it were to change that unit volumes increased by 10% it would give back 2% on the incremental dollar purchases, and if it was 111%-115% it would be 3%.. etc.....

right now I have each % in columns and i have to manually check the % increases and it takes way too long


so right now there are 4 diffrent scales 101%-105% 106%-110%

111%-115% and 115% + so I need 4 arguments and I dont know where to begin If anyone has some insight it would be greatly appreciated and if u need more info just ask!!

Thanks again

 

[Blade Hunter]

Can you post some sample data and what you expect the results to be?

 

[darrenciccone]

I will try and post an example of what I am talking about but I have hard time pasting images on this board, I downloaded the HTMLeditor program but im not doing something right.

 

[darrenciccone]

I will try and post an example of what I am talking about but I have hard time pasting images on this board, I downloaded the HTMLeditor program but im not doing something right.

 

[shemayisroel]

I will try and post an example of what I am talking about but I have hard time pasting images on this board, I downloaded the HTMLeditor program but im not doing something right.

Try this, it may be a little easier to use...

http://www.excel-jeanie-html.de/index.php?f=1

 

[darrenciccone]

Ok thanks, im goin to try right now appreciate the help!

 

[darrenciccone]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	UNITS						$ SALES
2	*	*	YTD Oct	*	YTD Oct	*	%	*	YTD Oct	*	*
3	*	*	07	*	08	*	Change	*	08	*	*
4	*	*	*	*	*	*	*	*	*	*	*
5	GM	*	DATA'!D12]* *95,941	*	DATA'!F12]*101,975	*	6.29%	*	DATA'!E12]$40,658	*	*
6	FORD	*	DATA'!D13]*141,264	*	DATA'!F13]*156,655	*	10.90%	*	DATA'!E13]$60,193	*	*
7	CHRYSLER	*	DATA'!D14]*100,611	*	DATA'!F14]*108,920	*	8.26%	*	DATA'!E14]$40,605	*	*
8	HONDA	*	DATA'!D15]* *52,620	*	DATA'!F15]* *48,881	*	-7.11%	*	DATA'!E15]$15,727	*	*
9	TOYOTA	*	DATA'!D16]* *27,156	*	DATA'!F16]* *38,818	*	42.94%	*	DATA'!E16]$15,041	*	*
10	*	*	*	*	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*	*	*	*	*
12	*	*	*	*	*	*	*	*	*	*	*
13	*	*	*	*	Estimated Allowance Earned For				*	*	*
14	*	*	*	*	Increasing Volume				*	*	*
15	*	*	*	*	1.0%	1.5%	2.0%	3.0%	*	*	*
16	*	*	*	*	*	*	*	*	*	*	*
17	GM	*	6.3%	*	$407	*	*	*	*	*	*
18	FORD	*	10.9%	*	*	$903	*	*	*	*	*
19	CHRYSLER	*	8.3%	*	*	$609	*	*	*	*	*
20	HONDA	*	-7.1%	*	*	$236	*	*	*	*	*
21	TOYOTA	*	42.9%	*	*	*	$301	*	*	*	*
22	*	*	*	*	*	*	*	*	*	*	*
23	*	*	*	*	*	*	*	*	*	*	*
24	*	*	*	*	*	*	*	*	*	*	*
25	*	*	*	*	*	EST. TOTAL ALLOWANCE BONUS:	$2,455	*	*	*	*
26	*	*	*	*	*	*	*	*	*	*	*
Sheet1

 

[darrenciccone]

I would really like to elimate the diffrent % columns and just have it populate the bounus according to one formula!! i.e. e15 - h15 and just have one column for the bonuse and the formula contain the diffrent assumptions and %'s

 

[darrenciccone]

Bump

 

[riaz]

Hi, please clarify a couple of points.

1) Honda went down in unit sales, but you are still giving a bonus. Why?

2) Toyota went up by 42%, but you are not giving it top bonus. Why?