IF statements

[pbolli1]

Hi,

The company I work for is having me create an interactive excel sheet. I know that this is probably going to be using an if statement but I cant figure out how to make this work. Basically what this is supposed to do, is if you type in the number of participants in one cell, the fee will pop up in the other cell. This is to show clients what they will be paying.

The fees are below

1-15 participants = $2500
16-50 participants = $2500 plus $70 for each participant over 15
51-100 participants = $4950 plus $65 for each participant over 50
101-500 participants = $8200 plus $60 for each participant over 100
501-1,000 participants = $32,200 plus $55 for each participant over 500
1,001+ participants = $59,700 plus $50 for each participant over 1,000

any help is much appreciated, I've been trying to figure this out forever, Thanks!!!!

 

[Jonmo1]

Should it be Cummulative ?
i.e., if it was say 55 should the answer be
4950+(65*5)
OR
2500+2500+(70*35)+4950+(65*5)

 

[pbolli1]

if it was 55 the fee would be $5,275. Basically, 4,950 + 5*65

 

[63falcondude]

How's this?

Excel 2010

	A	B	C	D	E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Participants[/TD]
[TD="align: center"]Fees[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Participants[/TD]
[TD="align: center"]Fees[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]5275[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2500[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]5300[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]51[/TD]
[TD="align: center"]5275[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]101[/TD]
[TD="align: center"]8200[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]501[/TD]
[TD="align: center"]32200[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]1001[/TD]
[TD="align: center"]59700[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=LOOKUP(D2,A2:A8,B2:B8)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=IF(D2>15,2500+(D2-15)*70,2500)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B5[/TH]
[TD="align: left"]=IF(D2>50,4950+(D2-50)*65,4950)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B6[/TH]
[TD="align: left"]=IF(D2>100,8200+(D2-100)*60,8200)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B7[/TH]
[TD="align: left"]=IF(D2>500,32200+(D2-500)*55,32200)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B8[/TH]
[TD="align: left"]=IF(D2>1000,59700+(D2-1000)*50,59700)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Try:

	A	B	C	D	E	F
Participants	Base Rate	Rate per extra individual	Number of Individuals	Fees

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]5275[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]4950[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]8200[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]501[/TD]
[TD="align: right"]32200[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]59700[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=VLOOKUP(E2,A2:C7,2)+(E2-LOOKUP(E2,A2:A7)+1)*VLOOKUP(E2,A2:C7,3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Columns A:C is just your table. You can change this at any time and the formula will adapt. Then put the Number of participants in E2, and F2 will show the fee.

 

[Jonmo1]

Try something like this

Unknown
	A	B	C	D	E	F
1	12	2500		Participants	Fee	Fee Per Over
2	20	2850		1	2500	0
3	55	5275		16	2500	70
4	120	9400		51	4950	65
5	600	37700		101	8200	60
6	1200	69700		501	32200	55
7				1001	59700	50
Sheet3

Cell Formulas
Range	Formula
B1	=IFERROR(LOOKUP(A1,$D$2:$E$7)+(A1-LOOKUP(A1,$D$2:$D$7)+1)*LOOKUP(A1,$D$2:$F$7),0)

 

[pbolli1]

Thank you to everyone! That was a major help, I really appreciate it.

 

[Jonmo1]

You're welcome.