Add an amount on to membership, but not if already paying enough!

[trickster1978]

Hi,

I run a subscription club to a club. We are putting in place new pricing, as there is a huge amount of historical prices in there that we need to get back in line.

In summary, the new prices will be £40 per month. Some pay this already, so will hold that rate.. so pay a lot less... as low as £22 PM.

What I need to do is increase all rates in a spreadsheet by 10%... but within this take it to a MINIMUM of £30 and not increase the max of £40.

Bit of a newbie question probably.

Thanks for any help.

Sample

[TABLE="width: 500"]
<tbody>[TR]
[TD]£40[/TD]
[TD]Keep [/TD]
[/TR]
[TR]
[TD]£35 [/TD]
[TD]Increase 10%[/TD]
[/TR]
[TR]
[TD]£34[/TD]
[TD]Increase 10%[/TD]
[/TR]
[TR]
[TD]£22[/TD]
[TD]Increase to Min £30 [/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

Assuming your prices are numbers not text:

Excel Workbook
	A	B
1	Old Rate	New Rate
2	40	40.00
3	35	38.50
4	34	37.40
5	22	30.00
Sheet3

 

[trickster1978]

This is brilliant- thank you.

I have just seen that there are actually a couple over £40... how can I keep the max to £40, so no increase if over?

Assuming your prices are numbers not text:
Sheet3

	A	B
Old Rate	New Rate

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40.00[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]38.50[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]37.40[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]30.00[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 =IF(A2=40,40,IF(A2*1.1<=30,30,A2*1.1)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[JoeMo]

You are welcome. To hold max to 40 try:

=IF(A2>=40,40,IF(A2*1.1<=30,30,A2*1.1))

 

[jtakw]

Hi,

I think this will give you more accurate results to cap it at 40.

Book1
	A	B
1	Old Rate	New Rate
2	40	40
3	35	38.5
4	34	37.4
5	22	30
6	37	40
7	38	40
8	39	40
9	25	30
10	36	39.6
11	42	40
Sheet16

Cell Formulas
Range	Formula
B2	=IF(A2>=40/1.1,40,MAX(30,A2*1.1))

 

[trickster1978]

You guys are awesome - thanks.

If I want to do NOTHING if over £40, what would I do?
i.e not cap and reduce.. but keep as is.. so a £40.50 stays at that, no reduction (it may be easier to give other credit than reduce the DD subscriptions)

Richard

Hi,

I think this will give you more accurate results to cap it at 40.

	A	B
Old Rate	New Rate

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

[TD="align: center"]2[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]38.5[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]37.4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]40[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]40[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]40[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]39.6[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]40[/TD]

</tbody>

Sheet16​

[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"]B2[/TH]
[TD="align: left"]=IF(A2>=40/1.1,40,MAX(30,A2*1.1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

You guys are awesome - thanks.

If I want to do NOTHING if over £40, what would I do?
i.e not cap and reduce.. but keep as is.. so a £40.50 stays at that, no reduction (it may be easier to give other credit than reduce the DD subscriptions)

Richard

Then we add another IF test in front:

Book1
	A	B
1	Old Rate	New Rate
2	40	40
3	35	38.5
4	34	37.4
5	22	30
6	37	40
7	38	40
8	39	40
9	25	30
10	36	39.6
11	42	42
12	40.5	40.5
Sheet16

Cell Formulas
Range	Formula
B2	=IF(A2>=40,A2,IF(A2>=40/1.1,40,MAX(30,A2*1.1)))